¿Cómo puedo parametrizar una tabla y columna en una función personalizada de Postgres, seleccionando la llave primaria (PK) si el valor existe, de lo contrario insertarlo y aun así devolver la PK?

19 Jul, 2023 Programación 0

Intentando hacer lo que se especifica en el título, ya logré que las funcionalidades de upsert funcionen, sin embargo, cuando intento parametrizarlo, me siento perdido y no puedo depurarlo.

Mi consulta:

CREATE OR REPLACE FUNCTION custom_upsert(target_value_input text, 
                                             target_table_input text,
                                             target_column_input text,
                                             OUT pk_output int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      execute 'SELECT id '
      ' FROM '  || target_table_input || 
      ' WHERE ' || target_column_input || ' = ' || target_value_input ||
      ' INTO pk_output';

<pre><code>  EXIT WHEN FOUND;

  execute 'INSERT  INTO ' || target_table_input || ' AS o ( ' || target_column_input || ' )'
  ' VALUES ( ' || target_value_input || ' ) '
  ' ON CONFLICT ( ' || target_column_input || ' ) DO NOTHING '
  ' RETURNING o.id'
  ' INTO pk_output';

  EXIT WHEN FOUND;
</code></pre>

END LOOP;
END
$func$;
 ```

Ahora, cuando intento usar la función, obtengo:

ERROR: syntax error at or near “INTO”
LINE 1: …module WHERE artifact_id = artifact_id_example_1 INTO pk_ou…
^
QUERY: SELECT id FROM maven_module WHERE artifact_id = artifact_id_example_1 INTO pk_output
CONTEXT: PL/pgSQL function custom_upsert(text,text,text) line 4 at EXECUTE


Lo que me desconcierta de esto es el hecho de que esta sintaxis funciona bien en una versión no parametrizada:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=765389a746d3a392bc646fbedb7ed3b3

Mis intentos de parametrización:

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1bffab45d8a9587342a7c3253ea35fc8

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=de6ba235aa21dae33b922f8fddac3b63

Muchas gracias de antemano, es la primera vez que publico, así que si hay algo que deba hacer de manera diferente al hacer una pregunta, agradezco los comentarios.

edición: esta es mi llamada a la función:

— debería devolver pk del identificador de artefacto existente
SELECT custom_upsert(‘identificador_artefacto_1’, ‘maven_module’, ‘artifact_id’);

— debería devolver pk del nuevo identificador de artefacto
SELECT custom_upsert(‘identificador_artefacto_2’, ‘maven_module’, ‘artifact_id’);
“`

davy.ai

July 19, 2023 at 1:36 pm

El problema con la versión parametrizada de la función es un error de sintaxis en la declaración EXECUTE. Al utilizar variables en una declaración SQL dinámica, deben concatenarse con la cadena utilizando el operador ||.

Para solucionar el error, cambie INTO pk_output en la declaración EXECUTE por INTO pk_output; y concatene las variables utilizando el operador ||. Aquí está la función corregida:

CREATE OR REPLACE FUNCTION custom_upsert(
    target_value_input text, 
    target_table_input text,
    target_column_input text,
    OUT pk_output int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      execute 'SELECT id 
      FROM '  || target_table_input || 
      ' WHERE ' || target_column_input || ' =  $1'
      INTO pk_output using target_value_input;

      EXIT WHEN FOUND;

      execute 'INSERT  INTO ' || target_table_input || ' AS o ( ' || target_column_input || ' )
      VALUES ( $1 ) 
      ON CONFLICT ( ' || target_column_input || ' ) DO NOTHING 
      RETURNING o.id'
      INTO pk_output using target_value_input;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;

Observe el uso de using en la declaración EXECUTE para pasar los valores de los parámetros.

Con la función corregida, la siguiente consulta debería funcionar:

SELECT custom_upsert('artifact_id_example_1', 'maven_module', 'artifact_id');
SELECT custom_upsert('artifact_id_example_2', 'maven_module', 'artifact_id');

¡Espero que esto ayude!

Answer

davy.ai

July 19, 2023 at 1:36 pm
El problema con la versión parametrizada de la función es un error de sintaxis en la declaración EXECUTE. Al utilizar variables en una declaración SQL dinámica, deben concatenarse con la cadena utilizando el operador ||.

Para solucionar el error, cambie INTO pk_output en la declaración EXECUTE por INTO pk_output; y concatene las variables utilizando el operador ||. Aquí está la función corregida:
```
CREATE OR REPLACE FUNCTION custom_upsert(
    target_value_input text, 
    target_table_input text,
    target_column_input text,
    OUT pk_output int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      execute 'SELECT id 
      FROM '  || target_table_input || 
      ' WHERE ' || target_column_input || ' =  $1'
      INTO pk_output using target_value_input;

      EXIT WHEN FOUND;

      execute 'INSERT  INTO ' || target_table_input || ' AS o ( ' || target_column_input || ' )
      VALUES ( $1 ) 
      ON CONFLICT ( ' || target_column_input || ' ) DO NOTHING 
      RETURNING o.id'
      INTO pk_output using target_value_input;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;
```
Observe el uso de using en la declaración EXECUTE para pasar los valores de los parámetros.

Con la función corregida, la siguiente consulta debería funcionar:
```
SELECT custom_upsert('artifact_id_example_1', 'maven_module', 'artifact_id');
SELECT custom_upsert('artifact_id_example_2', 'maven_module', 'artifact_id');
```
¡Espero que esto ayude!

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

¿Cómo puedo parametrizar una tabla y columna en una función personalizada de Postgres, seleccionando la llave primaria (PK) si el valor existe, de lo contrario insertarlo y aun así devolver la PK?

Answer

davy.ai