pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinstead of trigger in pg
2+ messages / 2 participants
[nested] [flat]
* instead of trigger in pg
@ 2005-12-28 15:28 J Crypter <jcrypter@yahoo.de>
0 siblings, 1 reply; 2+ messages in thread
From: J Crypter @ 2005-12-28 15:28 UTC (permalink / raw)
To: pgsql-sql
Hi,
I would like to implement a 1:n relation between two
tables.
An auto-generated number should be used as primary key
which connects both tables.
Example:
table 1:
name | number (prim_key)
table 2:
country | number_table1 (foreign key)
View:
number_table1 | name | country
I would like to insert into both tables in one step.
"Insert into view (name,
country)values('name','country');"
Oracle supports an instead-of-trigger but Postgres
doesn't do this.
CREATE or replace FUNCTION insertInto() RETURNS
trigger AS '
BEGIN
insert into table1(name)values(NEW.name);
insert into
table2(number,country)values(NEW.number,NEW.country);
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER insert_TR BEFORE INSERT OR UPDATE ON
view
FOR EACH ROW EXECUTE PROCEDURE insertInto();
I would like to implement something like this but I
don't know how to do.
Thank you for you help!
Jakob
___________________________________________________________
Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: instead of trigger in pg
@ 2005-12-28 16:03 Jaime Casanova <systemguards@gmail.com>
parent: J Crypter <jcrypter@yahoo.de>
0 siblings, 0 replies; 2+ messages in thread
From: Jaime Casanova @ 2005-12-28 16:03 UTC (permalink / raw)
To: J Crypter <jcrypter@yahoo.de>; +Cc: pgsql-sql
On 12/28/05, J Crypter <jcrypter@yahoo.de> wrote:
> Hi,
>
> I would like to implement a 1:n relation between two
> tables.
> An auto-generated number should be used as primary key
> which connects both tables.
> Example:
>
> table 1:
> name | number (prim_key)
>
> table 2:
> country | number_table1 (foreign key)
>
> View:
> number_table1 | name | country
>
> I would like to insert into both tables in one step.
> "Insert into view (name,
> country)values('name','country');"
>
> Oracle supports an instead-of-trigger but Postgres
> doesn't do this.
>
postgres uses rules for this situation... and yes there are INSTEAD OF rules...
> CREATE or replace FUNCTION insertInto() RETURNS
> trigger AS '
> BEGIN
> insert into table1(name)values(NEW.name);
> insert into
> table2(number,country)values(NEW.number,NEW.country);
>
> RETURN NEW;
> END;
> ' LANGUAGE plpgsql;
>
> CREATE TRIGGER insert_TR BEFORE INSERT OR UPDATE ON
> view
> FOR EACH ROW EXECUTE PROCEDURE insertInto();
>
>
> I would like to implement something like this but I
> don't know how to do.
>
> Thank you for you help!
>
> Jakob
>
>
>
>
>
>
> ___________________________________________________________
> Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-28 16:03 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-28 15:28 instead of trigger in pg J Crypter <jcrypter@yahoo.de>
2005-12-28 16:03 ` Jaime Casanova <systemguards@gmail.com>
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