0

Can someone politely explain this craziness?

INSERT INTO "dbo"."UserProfile" ("FirstName")
VALUES('John')
RETURNING "UserProfileId" INTO _UserProfileId;

throws an ambiguous reference error, however this correctly executes:

INSERT INTO "dbo"."UserProfile" ("FirstName")
VALUES('John')
RETURNING "dbo"."UserProfile"."UserProfileId" INTO _UserProfileId;

_UserProfileId is a declared integer variable. I couldn't find any references to this syntax in the manual or why on earth this would be ambiguous in any way.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Michael Brown
  • 1,585
  • 1
  • 22
  • 36
  • Is this from a plpgsql function definition? Can you provide some more complete code snippets including the complete function definition as well as the table definition? – redneb Sep 01 '16 at 03:01
  • Yes it is. It's a snippet from within a (large) plpgsql function. It's also not just INSERT statements but this error appears in other functions as well seemingly random ways. Is it somehow clashing with column names defined in a RETURNS TABLE() ? I can't narrow down the exact cause. – Michael Brown Sep 01 '16 at 03:43
  • Well, I cannot reproduce this. You have to provide a minimal complete example. – redneb Sep 01 '16 at 08:50

1 Answers1

7

IN and OUT parameters (including columns in RETURNS TABLE) are visible inside every SQL command in the body of a plpgsql function.

If you have columns of the same name in your query, you have to table-qualify them to make it unambiguous. In your case, the table name would do:

... RETURNING "UserProfile"."UserProfileId" INTO _UserProfileId;

Details in the manual here.

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228