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 tbl (
  t_id serial PRIMARY KEY
, col1 text
, col2 text
, col3 text
);

INSERT INTO tbl (t_id, col1, col2, col3) VALUES
  (DEFAULT, 'foo1', 'bar1', 'baz1');  -- aside: you can use the keyword "DEFAULT"

INSERT INTO tbl (col1, col2, col3) VALUES
  ('foo2', 'bar2', 'baz2')  -- but rather just omit those columns (if possible)
;


CREATE OR REPLACE FUNCTION f_clone_row(in_table_name regclass
                                     , in_row_pk int
                                     , in_override_values hstore
                                     , OUT pk_new int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _pk   text;  -- name of PK column
   _cols text;  -- list of names of other columns
BEGIN
   -- Get name of PK column
   SELECT INTO _pk  a.attname
   FROM   pg_catalog.pg_index     i
   JOIN   pg_catalog.pg_attribute a ON a.attrelid = i.indrelid
                                   AND a.attnum   = i.indkey[0]  -- single PK col!
   WHERE  i.indrelid = in_table_name
   AND    i.indisprimary;

   -- Get list of columns excluding PK column
   SELECT INTO _cols  string_agg(quote_ident(attname), ',')
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = in_table_name  -- regclass used as OID
   AND    attnum > 0                -- exclude system columns
   AND    attisdropped = FALSE      -- exclude dropped columns
   AND    attname <> _pk;           -- exclude PK column

   -- INSERT cloned row with override values, returning new PK
   EXECUTE format('
      INSERT INTO %1$I (%2$s)
      SELECT %2$s
      FROM  (SELECT (t #= $1).* FROM %1$I t WHERE %3$I = $2) x
      RETURNING %3$I'
    , in_table_name, _cols, _pk)
   USING   in_override_values, in_row_pk  -- use override values directly
   INTO    pk_new;                        -- return new pk directly
END
$func$;

CREATE TABLE



INSERT 0 1



INSERT 0 1



CREATE FUNCTION


-- status
TABLE tbl;


t_id
col1
col2
col3


1
foo1
bar1
baz1

2
foo2
bar2
baz2



SELECT 2


-- insert cloned row
SELECT f_clone_row('tbl', 1, hstore '"col1"=>"foo_new", "col2"=>"bar_new"');


f_clone_row


3



SELECT 1


-- new status
TABLE tbl;


t_id
col1
col2
col3


1
foo1
bar1
baz1

2
foo2
bar2
baz2

3
foo_new
bar_new
baz1



SELECT 3