Hi @cole-brand_2889 ,
In general, that error message means some kind of code problem. Like, using "DatabasePathText" on a multi-table join without specifying which table/alias it belongs to.
However given the queries (see below code), I don't see the problem. I haven't seen the error on PostgreSQL... so that must mean it's Aurora Postgres specific?
According to ChatGPT, "Aurora PostgreSQL is stricter about recordset functions and want a relation alias before the column definition list.", but who knows if that's true. About the only way to test this theory is to modify the function code in your database. I've pasted it below, and you should be able to just run that to "edit" the code.
It'll get updated during any normal upgrade/downgrade, so no real worry.
The first change suggested was to add a BPT here:
WITH BlobPackages_Table AS (
SELECT * FROM jsonb_to_recordset("@BlobPackages_Table") AS BPT("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
),
I don't see how that could work, but who knows. Another suggested change was this:
WITH BlobPackages_Table AS (
SELECT * FROM jsonb_to_recordset(COALESCE("@BlobPackages_Table", '[]'::jsonb)) AS BPT("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
),
Although, I also don't believe that word work, since the @BlobPackages_Table would not be null. But again who knows.
Anyway... that's where I would start. It might be something else altogether, but I can't see it and I guess my ChatGPT prompt didn't spot it either.
CREATE OR REPLACE PROCEDURE "DockerBlobs_RecordScanData"
(
"@DockerBlob_Id" INT,
"@BlobInfo_Configuration" XML,
"@BlobPackages_Table" JSONB
)
LANGUAGE plpgsql
AS $$
BEGIN
IF "@BlobInfo_Configuration" IS NULL THEN
DELETE FROM "DockerBlobInfos" WHERE "DockerBlob_Id" = "@DockerBlob_Id";
ELSE
INSERT INTO "DockerBlobInfos" ("DockerBlob_Id", "BlobInfo_Configuration")
VALUES ("@DockerBlob_Id", "@BlobInfo_Configuration")
ON CONFLICT DO
UPDATE SET "BlobInfo_Configuration" = "@BlobInfo_Configuration"
WHERE "DockerBlob_Id" = "@DockerBlob_Id";
END IF;
UPDATE "DockerBlobs"
SET "LastScan_Date" = CURRENT_TIMESTAMP
WHERE "DockerBlob_Id" = "@DockerBlob_Id";
WITH BlobPackages_Table AS (
SELECT * FROM jsonb_to_recordset("@BlobPackages_Table") AS ("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
),
packagesToRemove AS (
SELECT *
FROM "DockerBlobPackages" DBP
LEFT JOIN BlobPackages_Table BPT
ON BPT."DatabasePath_Text" = DBP."DatabasePath_Text"
AND BPT."PackageVersion_Id" = DBP."PackageVersion_Id"
WHERE DBP."DockerBlob_Id" = "@DockerBlob_Id"
AND BPT."PackageVersion_Id" IS NULL
),
deletes AS (
DELETE FROM "DockerBlobPackages" DBP
USING packagesToRemove PTR
WHERE DBP."DockerBlob_Id" = "@DockerBlob_Id"
AND DBP."DatabasePath_Text" = PTR."DatabasePath_Text"
AND DBP."PackageVersion_Id" = PTR."PackageVersion_Id"
),
newBlobPackages AS (
SELECT BPT.*
FROM BlobPackages_Table BPT
LEFT JOIN "DockerBlobPackages" DBP
ON DBP."DockerBlob_Id" = "@DockerBlob_Id"
AND BPT."DatabasePath_Text" = DBP."DatabasePath_Text"
AND BPT."PackageVersion_Id" = DBP."PackageVersion_Id"
WHERE DBP."PackageVersion_Id" IS NULL
)
INSERT INTO "DockerBlobPackages"
SELECT "@DockerBlob_Id",
"DatabasePath_Text",
"PackageVersion_Id"
FROM newBlobPackages BPT;
END $$;
// also I do realize the json input is not a great way to handle this, but it's how we needed to port a few things from SQL Server to maintain parity in behavior
Let us know if you find anything! Thanks, Alana
