plpgsql: Cast 'new' record for insert statement-CodePudding

I try to build a history inside a table. Part of the history function are the following table attributes:

id: uuid
historycounter: int8

Each time a dataset is added to the table its historycounter is set to zero. Each time a dataset is update the historycounter should be increased by one. A before insert or update or delete on <table> for each row should do all the work.

For the update part of the trigger function this looks as follows:

execute format('update %I.%I set historycounter = historycounter   1 where id = $1', schema_name, tab_name) using old.id;
--  execute format('insert into %I.%I select $1', schema_name, tab_name) using new::text:xx;
execute format('insert into %I.%I values ($1)', schema_name, tab_name) using new.*::text;
return null;

Updating the historycounter works fine. But what ever I try, I did not find a way to do the conversion from UPDATE into INSERT. I like to build it on the most generic way possible. So knowing and adding each attribute of the dataset (table) is not a way.

As far as I know new and old are of the type record. Casting a record to row should do the trick, but HOW?

Any hints are welcome!

CodePudding user response：

You need to unpack composite value inside INSERT statement. This is SQL feature (not plpgsql feature). So using star * in USING clause should not to work. NEW and OLD are not record variables. They are composite variables with known type.

create table foo(a int, b varchar, c int);
create table foo2( a int, b varchar, c int);

create or replace function foo_insert_trg()
returns trigger as $$
begin
  execute 'insert into foo2 values($1.*)' using new;
  return null;
end;
$$ language plpgsql;

insert into foo values(10, 'ahoj', 20);
INSERT 0 0
select * from foo2;
┌────┬──────┬────┐
│ a  │  b   │ c  │
╞════╪══════╪════╡
│ 10 │ ahoj │ 20 │
└────┴──────┴────┘
(1 row)