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.