Insert values from a record variable into a subclass table

Question

My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME schema. I want the output_table to have the same schema PLUS some extra computed columns.

Will I have to use a less elegant solution than the one linked? Or is there a solution where I can append columns onto the _tbl_in record before I insert it to the subclass _tbl_out?

Here is the code for the previous solution for reference:

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN
FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record
EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
   USING _tbl_in;
END LOOP;
END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 being the tables with identical schema.

Corey Cole · Answer 1 · 2017-02-14T23:33:52.050

I've realized my problem has a very simple solution. As long as we are sure about order of the schema and the extra columns are after all the original columns, my problem is simply solved by adding the values to the SELECT of the INSERT INTO

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record
   computed_column1 := 'some value';
   EXECUTE format('INSERT INTO %s SELECT $1.*, computed_column1', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 are tables with identical schemas, but t1 has added a varchar column. The name of the column doesn't matter here because we're only using the order of the columns as they appear in the schemas of the two tables.

score 0 · Accepted Answer · edited Jun 15 '20 at 09:05

Your goal is ...

a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in.

And by "subclass" you mean there are additional columns at the end of _tbl_out.

So the function shall take two table names and copy contents from the first to the second - plus one or more additional (computed?) columns. The only thing we know about table definitions: the second has additional dangling columns.

You only need a polymorphic parameter (ANYELEMENT) to pass in or return values of dynamic type - or at least work with a variable of the type in the function body. What you demonstrate does not return anything (RETURNS void) and also has no need for the input value or type. You only use the table name derived from the input parameter and the parameter itself as auxiliary variable - which goes away after simplifying the function.

Unless you have computations depending on the type or value of the polymorphic parameter, two regclass parameters and a single dynamic statement can do the job, and much faster, too. (Or even just two text parameters passing valid table names, but regclass is more reliable):

CREATE OR REPLACE FUNCTION pg_temp.gesio(_tbl_in regclass, _tbl_out regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format($$
      INSERT INTO %s       -- *no* target column list
      SELECT *
           , 'some value'  -- AS computed_column1  -- column alias only for documentation
          -- more ?
      FROM   %s$$$         -- ORDER BY ???
    , _tbl_out, _tbl_in);
END
$func$  LANGUAGE plpgsql;

I added a note to my old answer you referenced that was not clear enough:

Insert values from a record variable into a table

Performance with big tables

A major factor for the resulting cost is the Write Ahead Log (WAL) that has to be maintained for this form. There are ways to avoid that completely and make it substantially faster: Write to a completely new table within the transaction, so it's invisible to the rest of the world until committed. Details:

What causes large INSERT to slow down and disk usage to explode?

Aside: It's a function, not a stored procedure. Postgres does not have stored procedures, strictly speaking. (The most notable difference: functions always run inside a single transaction scope.)

Insert values from a record variable into a subclass table

2 Answers2

Performance with big tables

Linked