Insert values from a record variable into a subclass table 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.
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.