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 '  || 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$;
-- returns existing artifact_id
SELECT custom_upsert('artifact_id_example_1', 'maven_module', 'artifact_id');  

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


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


SELECT * FROM maven_module;


id
artifact_id


1
artifact_id_example_1