A section of a PL/pgSQL function looks like this:
INSERT INTO assistant.accesstoken
(
submission_id,
token,
expires
)
VALUES
(
v_submission_id,
assistant.pseudo_encrypt(v_submission_id),
CURRENT_TIMESTAMP + v_token_duration
)
ON CONFLICT (submission_id)
DO UPDATE SET
expires = CURRENT_TIMESTAMP + v_token_duration
RETURNING token
INTO v_accesstoken;
...and gives me a complaint:
psycopg.errors.AmbiguousColumn: column reference "submission_id" is ambiguous
LINE 13: ON CONFLICT (submission_id)
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: INSERT INTO assistant.accesstoken
(
submission_id,
token,
expires
)
VALUES
(
v_submission_id,
assistant.pseudo_encrypt(v_submission_id),
CURRENT_TIMESTAMP + v_token_duration
)
ON CONFLICT (submission_id)
DO UPDATE SET
expires = CURRENT_TIMESTAMP + v_token_duration
RETURNING token
CONTEXT: PL/pgSQL function assistant.evaluation_begin(character varying,character varying) line 115 at SQL statement
There is no variable named submission_id. Name only exists as a column name.
Unfortunately, it seems to be an error to specify table for the submission_id:
ERROR: syntax error at or near ")"
LINE 140: ON CONFLICT (accesstoken.submission_id)
How do I fix this then? How to prefix conflict_target for ON CONFLICT in a way that it is accepted?
Additional, can someone point me to a source where I could learn how variables and ON CONFLICT can go together? How would an INSERT be interested on a "conflict" in a PL/pgSQL variable when inserting a row, and what would it even mean? I am genuinely curious...
Does PostgreSQL have overzealous checking or something? There are no variables, anywhere ever in this application, that would not be prefixed with in_, out_, inout_, r_ or v_, ...meaning that there is no ambiguity of this kind that I could see.
Looks like this was closed during the night, so I won't bother with this topic much more than just try to write this open a little better:
In the comments, klin provided a link 37.5.9. SQL Functions Returning TABLE which explains why this happened, however the question remains unanswered: How does one prefix the conflict_target to tell PostgreSQL that it is a column, not a variable?
Furthermore, the linked "duplicate question" does not deal with the above. I thought this site wanted to accumulate knowledge and information - now this hasty and incorrect closure makes it seem like a support ticket handling (workaround found, close it). Well, if this is what you want of this site, then that's what you do.
Description of the issue. Because returning a TABLE is equivalent of returning a SETOF, the "column" names are then just variable names in the local scope, it seems (as opposed to be in a namespace of the anonymous table). Thus, the definition of the return TABLE which had submission_id (as shown below) was the cause of the ambiguity and renaming that, UPSERT's ON CONFLICT column / conflict_target is no longer ambiguous. (May have felt like a bug, but its not...)
CREATE OR REPLACE FUNCTION
assistant.evaluation_begin(
in_course_id VARCHAR,
in_uid VARCHAR
)
RETURNS TABLE (
submission_id INTEGER,
accesstoken INTEGER
)
LANGUAGE PLPGSQL
SECURITY DEFINER
VOLATILE
CALLED ON NULL INPUT
AS $$
This information hopefully allows others to see this condition (should they experience a similar issue) and this will certainly help working around it, even if doesn't provide the syntax/tools to maintain the call interface intact.
The second question also remains unanswered: In what way can a variable be a valid ON CONFLICT target and what does it even mean? It could be that such a thing doesn't even exist, and might just be because the same error string is used in all ambiguity cases. I personally suspect that this is the case.
But as this is closed now, we'll leave it at "problem solved, workaround found" state. Thank You for klin - the link was very helpful!