Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE maven_module (id serial PRIMARY KEY, artifact_id text UNIQUE);
INSERT INTO maven_module(artifact_id) VALUES ('artifact_id_example_1');
1 rows affected

Select * from maven_module


id
artifact_id


1
artifact_id_example_1


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 '
      + quote_ident(target_table_input) 
      + ' WHERE ' 
      + quote_ident(target_column_input) + ' = ' + quote_ident(target_value_input)
      + ' INTO ' pk_output;

      EXIT WHEN FOUND;
      
      execute 'INSERT  INTO ' 
      + quote_ident(target_table_input) + 'AS o (' + quote_ident(target_column_input) + ')'
      + ' VALUES (' + quote_ident(target_value_input) + ')'
      + ' ON CONFLICT (' + quote_ident(target_column_input) + ') DO NOTHING'
      + ' RETURNING o.id'
      + ' INTO ' pk_output;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;
-- should return existing artifact_id
SELECT custom_upsert('artifact_id_example_1', 'maven_module', 'artifact_id');  

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

SELECT * FROM maven_module;
ERROR:  operator does not exist: unknown + text
LINE 2:       + quote_ident(target_table_input) 
              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  'SELECT id FROM '
      + quote_ident(target_table_input) 
      + ' WHERE ' 
      + quote_ident(target_column_input) + ' = ' + quote_ident(target_value_input)
      + ' INTO ' pk_output
CONTEXT:  PL/pgSQL function custom_upsert(text,text,text) line 4 at EXECUTE