On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky <Jenda@krynicky.cz> wrote:
CREATE OR REPLACE FUNCTION public.findorcreatelocker( lockeruuid text, ipaddress text) RETURNS TABLE("Id" integer, "Created" timestamp without time zone, "Uuid" text, "State" integer, "ConnectionStatus" integer, "LastConnected" timestamp without time zone, "DoorColumns" bytea, "IpAddress" text, "LastReportCreated" timestamp without time zone)
INSERT INTO "Lockers" ("Uuid", "IpAddress", "State", "DoorColumns", "ConnectionStatus", "LastConnected", "LastReportId", "LongOpenedDoors", "Created", "Updated") VALUES (lockerUuid, ipAddress, 0/*new*/, null, 0/*connected*/, current, null, null, current, current) ON CONFLICT ("Uuid") DO NOTHING; end if;
How the (censored) am I supposed to write the ON CONFLICT () clause so that it works?
Like this it reports:
ERROR: column reference "Uuid" is ambiguous LINE 3: ON CONFLICT ("Uuid") ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column.
THERE IS NO (CENSORED) VARIABLE "Uuid"!
Yes, there is. RETURNS TABLE (... "Uuid" text ... )
Changing that to something else should remove the ambiguity. I agree it is not an ideal solution though. I'm not sure what other options exist though.
If I drop the quotes and use just ON CONFLICT (Uuid) I get
ERROR: column "uuid" does not exist LINE 3: ON CONFLICT (Uuid) ^ HINT: Perhaps you meant to reference the column "Lockers.Uuid".
Yes, thank you, that's exactly what I meant. That's what I wrote too, you (censored). I didn't write uuid, I wrote Uuid, you imbecile!
This one is on you for removing the double quotes that your choice of identifier names forces you to basically put everywhere.
If I try to include the table name as ON CONFLICT (Lockers.Uuid) or
Even if you didn't get a syntax error that isn't the name of your column...
ON CONFLICT ("Lockers"."Uuid"), I get a syntax error.
This is the one that should work so if its giving a syntax error it is the one worth questioning.
If I specify it as ON CONFLICT ("Lockers.Uuid") I get quite understandably ERROR: column "Lockers.Uuid" does not exist LINE 3: ON CONFLICT ("Lockers.Uuid")
Yes, the double quotes in the earlier "Perhaps you meant" error message are a readability thing, not a "write this instead" thing.
