Una sección de una función PL/pgSQL se ve así:
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;...y me da una queja:
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 No hay una variable llamada submission_id . El nombre solo existe como nombre de columna.
Desafortunadamente, parece ser un error especificar la tabla para submission_id :
ERROR: syntax error at or near ")" LINE 140: ON CONFLICT (accesstoken.submission_id) ¿Cómo soluciono esto entonces? ¿Cómo prefijar conflict_target para ON CONFLICT de manera que sea aceptado?
Además, ¿alguien puede señalarme una fuente donde pueda aprender cómo las variables y ON CONFLICT pueden ir juntas? ¿Cómo estaría interesado un INSERT en un "conflicto" en una variable PL/pgSQL al insertar una fila, y qué significaría? tengo mucha curiosidad...
¿PostgreSQL tiene una verificación demasiado entusiasta o algo así? No hay variables, en ninguna parte de esta aplicación, que no tengan el prefijo in_ , out_ , inout_ , r_ o v_ , ... lo que significa que no hay ambigüedad de este tipo que yo pueda ver.
Parece que esto estuvo cerrado durante la noche, así que no me molestaré con este tema mucho más que tratar de escribir esto abierto un poco mejor:
En los comentarios, klin proporcionó un enlace 37.5.9. Funciones de SQL que devuelven TABLE que explica por qué sucedió esto, sin embargo, la pregunta sigue sin respuesta: ¿Cómo se antepone el conflict_target para decirle a PostgreSQL que es una columna, no una variable?
Además, la "pregunta duplicada" vinculada no trata con lo anterior. Pensé que este sitio quería acumular conocimiento e información; ahora este cierre precipitado e incorrecto hace que parezca un manejo de un ticket de soporte (se encontró una solución, ciérrelo). Bueno, si esto es lo que quieres de este sitio, entonces eso es lo que haces.
Descripción del problema. Debido a que devolver una TABLE es equivalente a devolver un SETOF , los nombres de "columna" son solo nombres de variables en el ámbito local, al parecer (en lugar de estar en un espacio de nombres de la tabla anónima). Por lo tanto, la definición de la TABLE de retorno que tenía submission_id (como se muestra a continuación) fue la causa de la ambigüedad y el cambio de nombre, la columna ON CONFLICT / conflict_target de UPSERT ya no es ambigua. (Puede haberse sentido como un error, pero no lo es...)
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 $$Con suerte, esta información permite que otros vean esta condición (en caso de que experimenten un problema similar) y esto ciertamente ayudará a solucionarlo, incluso si no proporciona la sintaxis/herramientas para mantener intacta la interfaz de llamada.
La segunda pregunta también queda sin respuesta: ¿De qué manera una variable puede ser un objetivo válido de ON CONFLICT y qué significa? Podría ser que tal cosa ni siquiera exista, y podría deberse a que se usa la misma cadena de error en todos los casos de ambigüedad. Personalmente sospecho que este es el caso.
Pero como esto está cerrado ahora, lo dejaremos en el estado "problema resuelto, solución alternativa encontrada". Gracias por klin - ¡el enlace fue muy útil!