INSERT INTO ... RETURNING - ambiguous column reference

Question

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.

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
– redneb, Commented Sep 1, 2016 at 3: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
– Michael Brown, Commented Sep 1, 2016 at 3:43
Well, I cannot reproduce this. You have to provide a minimal complete example. — redneb
– redneb, Commented Sep 1, 2016 at 8:50

Erwin Brandstetter · Accepted Answer · 2021-10-10 03:39:37Z

8

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.

answered Sep 2, 2016 at 3:41

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT INTO ... RETURNING - ambiguous column reference

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related