Use insert trigger to copy a modified NEW record to another table, with unknown columns

Question

I use a Postgres extension (promscale) that dynamically creates tables with similar schemas: the tables all have a 'time' and a 'value' columns, then a number of labels columns. Upon insert in any of those tables I need to make store a copy of the new row into a sibling table, after recalculating the value. This question almost has the answer, but I can't explicitly access sample.value on the anyelement variable. Here is what I tried:

create or replace function increase_trigger() returns trigger as $update_increase$
declare
 tbl_out varchar;
begin
 tbl_out := TG_TABLE_NAME || ":increase";
 select calc_increase(new, tbl_out);
end;
$update_increase$ language plpgsql;
create or replace procedure calc_increase(sample anyelement, tbl_out regclass) as $$
begin
 sample.value = sample.value + 1.0;
 execute format('insert into %s select 1ドル.*', tbl_out) using sample;
end; $$ language plpgsql;

Of course the parser complains that "sample.value" is not a known variable. I know the output table name and thus its composite type name, but I don't know how to use it in the function. I found could cast a my input sample (not tested):

execute format("select ROW(1ドル.*)::%I from 1ドル.*", tbl_out) using sample;

... but I can't put everything together to get something that works.

Any help appreciated!

Thanks!

[Edit2] The solution is indeed to use RECORD as a type instead of anyelement:

The complete code is as follows:

create table t1 (
 c1 varchar,
 c2 double precision
);
create table t2 (
 c1 varchar,
 c2 double precision
);
create or replace procedure calc_increase(sample record, tbl_out regclass) as $$
begin
 -- test
 c2.value = c2.value + 1.0;
 execute format('insert into %s select 1ドル.*', tbl_out) using sample;
end; $$ language plpgsql;
create or replace function update_increase() returns trigger as $update_increase$
declare
 increase record;
begin
 call calc_increase(new, 't2');
 return new;
end;
$update_increase$ language plpgsql;
create trigger t_increase before insert on t1 for each row 
 execute function update_increase();
insert into t1(c1,c2) values('foo', 10.0);

Answer 1

Looks like the underlying issue is that you're trying to pass the row the trigger is acting on as ANYELEMENT, which is for types; what you want here is RECORD.

Assuming you're using PostgreSQL 11 or later (example contains CALL syntax), this should work:

CREATE OR REPLACE FUNCTION increase_trigger()
 RETURNS TRIGGER
 LANGUAGE plpgsql
AS $$
DECLARE
 tbl_out VARCHAR;
BEGIN
 tbl_out := TG_TABLE_NAME || '_increase';
 CALL calc_increase(new, tbl_out);
 RETURN NEW;
END;
$$;

CREATE OR REPLACE PROCEDURE calc_increase(
 sample RECORD,
 tbl_out REGCLASS
)
 LANGUAGE plpgsql
AS $$
BEGIN
 sample.value = sample.value + 1.0;
 EXECUTE FORMAT('INSERT INTO %s VALUES (1ドル.*)', tbl_out) USING sample;
END;
$$;

Not tested for potential side-effects.

• Franck: Well, this yields a new error: SQL Error [55000]: ERROR: record "sample" is not assigned yet Detail: The tuple structure of a not-yet-assigned record is indeterminate. Where: SQL statement "SELECT sample.value + 1.0" PL/pgSQL function metering.calc_increase(record,regclass) line 4 at assignment SQL statement "CALL metering.calc_increase(new, 'metering.t2')" PL/pgSQL function update_increase() line 5 at CALL
• Ian Barwick: How is the trigger defined?
• Franck: yes my bad, I was using "for each statement". Using "for each row" I am back to the original error, I have edited my original question with all the details.
• Franck: ... and your solution works like a charm if I use the correct column names - thanks!
• Ian Barwick: Thanks for the follow-up!