pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to use record type
4+ messages / 2 participants
[nested] [flat]
* how to use record type
@ 2001-08-14 14:34 Horst Herb <hherb@malleenet.net.au>
0 siblings, 0 replies; 4+ messages in thread
From: Horst Herb @ 2001-08-14 14:34 UTC (permalink / raw)
To: pgsql-sql
Help please!
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Fwd: [SQL] how to use record type
@ 2001-08-16 22:24 Horst Herb <hherb@malleenet.net.au>
0 siblings, 1 reply; 4+ messages in thread
From: Horst Herb @ 2001-08-16 22:24 UTC (permalink / raw)
To: pgsql-novice@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to use record type
@ 2001-08-17 14:18 Jeff Eckermann <jeckermann@verio.net>
parent: Horst Herb <hherb@malleenet.net.au>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Eckermann @ 2001-08-17 14:18 UTC (permalink / raw)
To: Horst Herb <hherb@malleenet.net.au>; pgsql-novice@postgresql.org; pgsql-sql
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)
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to use record type
@ 2001-08-19 13:20 Horst Herb <hherb@malleenet.net.au>
parent: Jeff Eckermann <jeckermann@verio.net>
0 siblings, 0 replies; 4+ messages in thread
From: Horst Herb @ 2001-08-19 13:20 UTC (permalink / raw)
To: Jeff Eckermann <jeckermann@verio.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-08-19 13:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-14 14:34 how to use record type Horst Herb <hherb@malleenet.net.au>
2001-08-16 22:24 Fwd: [SQL] how to use record type Horst Herb <hherb@malleenet.net.au>
2001-08-17 14:18 ` Jeff Eckermann <jeckermann@verio.net>
2001-08-19 13:20 ` Horst Herb <hherb@malleenet.net.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox