Thread: Fwd: how to use record type

I have difficulties understanding how to use variable of "record" or "row"
type. How do I actually insert the variables OLD or NEW or a record type into
a table from within a trigger?

Like doing the following:

drop table th1;
create table th1(
id serial,
text text );

drop table th_audit;
create table th1_audit(
ts timestamp default now()
) inherits(th1);

drop function thaudit();
create function thaudit() returns opaque as '
begin
    -- I want to insert OLD into th_audit - how do I do this???
    return NEW;
end; ' language 'plpgsql';

drop trigger ta on th1;
create trigger ta before delete or update on th1
    for each row execute procedure thaudit();

Reagrds,
Horst

I have encountered this problem (in a different context), and could not find
a way to insert entire rows/records in the way that you appear to want.  But
it worked fine if I INSERTed explicitly, like:
INSERT INTO table VALUES (OLD.field1, OLD.field2, ..., OLD.fieldn);
That should work fine inside a plpgsql function.
----- Original Message -----
From: "Horst Herb" <hherb@malleenet.net.au>
To: <pgsql-novice@postgresql.org>; <pgsql-sql@postgresql.org>
Sent: Thursday, August 16, 2001 5:24 PM
Subject: Fwd: how to use record type


> I have difficulties understanding how to use variable of "record" or "row"
> type. How do I actually insert the variables OLD or NEW or a record type
into
> a table from within a trigger?
>
> Like doing the following:
>
> drop table th1;
> create table th1(
> id serial,
> text text );
>
> drop table th_audit;
> create table th1_audit(
> ts timestamp default now()
> ) inherits(th1);
>
> drop function thaudit();
> create function thaudit() returns opaque as '
> begin
> -- I want to insert OLD into th_audit - how do I do this???
> return NEW;
> end; ' language 'plpgsql';
>
> drop trigger ta on th1;
> create trigger ta before delete or update on th1
>     for each row execute procedure thaudit();
>
> Reagrds,
> Horst
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

On Saturday 18 August 2001 00:18, you wrote:
> I have encountered this problem (in a different context), and could not
> find a way to insert entire rows/records in the way that you appear to
> want.  But it worked fine if I INSERTed explicitly, like:
> INSERT INTO table VALUES (OLD.field1, OLD.field2, ..., OLD.fieldn);
> That should work fine inside a plpgsql function.

This is what I have been doing until recently. Rather ugly, as it is >250 
different tables which need this. Meaning that I had to manually write the 
trigger functions for each table, attribute by attribute.

At present, I solved the situation like that:

- all tables that need the trigger function inherit a parent table
- a python script scans all tables inheriting the parent table and then 
generates the trigger functions for them. I still execute the script manually 
after updating or inserting tables.
- now I am trying to find out how to implement a trigger on the system tables 
that will fire my Python script automatically whenever a table inheriting 
this special parent table is altered or created.

Sad that such an ugly hack is neccessary though.

Horst