How do I parameterize table & column in a Postgres-custom-function, selecting PK if value exists, otherwise insert it and return PK anyways?

Trying to do what I specified in the title, I already got the upsert-functionalities working, however when I try to parameterize it, I’m just out of my depth and can’t debug it.

My query:

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';

      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;
   END LOOP;
END
$func$;

​x
 
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';​      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;   END LOOP;END$func$;​

now when I try to use the function, I get:

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

 
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_outputCONTEXT:  PL/pgSQL function custom_upsert(text,text,text) line 4 at EXECUTE​

What puzzles me about this is the fact that this syntax works fine in an unparameterized version:

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

My attempts at parameterization:

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

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

Thank you very much in advance, first time posting so if there’s anything I should do differently when asking a question, I’m happy about feedback

edit: this is my function call:

-- should return pk of existing artifact_id
SELECT custom_upsert('artifact_id_example_1', 'maven_module', 'artifact_id');  

-- should return pk of new artifact_id
SELECT custom_upsert('artifact_id_example_2', 'maven_module', 'artifact_id');

 
-- should return pk of existing artifact_idSELECT custom_upsert('artifact_id_example_1', 'maven_module', 'artifact_id');  ​-- should return pk of new artifact_idSELECT custom_upsert('artifact_id_example_2', 'maven_module', 'artifact_id');​

Answer

Do not concatenate strings like that. The function format() makes your life much easier (safer), e.g.

EXECUTE format('INSERT INTO %1$I AS o (%2$I) 
                VALUES (%3$L) ON CONFLICT (%2$I) DO NOTHING RETURNING o.id',
                target_table_input,
                target_column_input,
                target_value_input) INTO pk_output;

 
EXECUTE format('INSERT INTO %1$I AS o (%2$I)                 VALUES (%3$L) ON CONFLICT (%2$I) DO NOTHING RETURNING o.id',                target_table_input,                target_column_input,                target_value_input) INTO pk_output;​

%I will wrap the identifiers with double quote, which is handy when tables or columns are case sensitive of contain special characters.
%L will wrap the literals with single quotes
1$, 2$ and 3$ are the variables positions provided in the format() call, which is quite handy if one variable is used more than once.

Demo: db<>fiddle

Advertisement

Answer