pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan I create working trigger on view
4+ messages / 3 participants
[nested] [flat]
* Can I create working trigger on view
@ 2002-10-17 14:40 Акулов Александр <akulov@ien.ru>
0 siblings, 0 replies; 4+ messages in thread
From: Акулов Александр @ 2002-10-17 14:40 UTC (permalink / raw)
To: pgsql-sql
Hello!
Please, help me!
Can I create working trigger on view?
The problem is:
I need a plpgsql function that execute on insert (or update,
or delete) into view and knows the *OLD* and *NEW*.
(Number of fields can be more than 16)
Something like this:
Create Sequence id;
Create Table t1(id1 int4, last_name varchar(32), time_create_t1);
Create Table t2(id2 int4, first_name varchar(32), time_create_t2);
Create View name as
Select * from t1 a, t2 b where a.id1=b.id2;
Create Rule rl_ins_nameas on INSERT to name do instead nothing;
-- without it trigger is not allowed
Create Function fn_ins_name() returns opaque as
'
Declare
v_time_create timestamp;
Begin
v_tm_create:=current_timestamp;
Insert into t1(id1, last_name, time_create_t1)
values(nextval(''id''), new.last_name, v_time_create);
Insert into t2(id2, first_name, time_create_t2)
values(currval(''id''), new.first_name, v_time_create);
return null;
End;
' Language 'plpgsql';
Create Trigger tr_ins_name before INSERT on now_cis_user
for each row execute procedure fn_ins_name;
This example is very simple. The trigger is not fired.
Sorry for my English.
--
Thanks for everybody.
Akulov Alexander.
e-mail:akulov@ien.ru
^ permalink raw reply [nested|flat] 4+ messages in thread
* Can I create working trigger on view
@ 2002-10-18 05:06 Акулов Александр <akulov@ien.ru>
0 siblings, 2 replies; 4+ messages in thread
From: Акулов Александр @ 2002-10-18 05:06 UTC (permalink / raw)
To: pgsql-sql
Hello!
Please, help me!
Can I create working trigger on view?
The problem is:
I need a plpgsql function that execute on insert (or update,
or delete) into view and knows the *OLD* and *NEW*.
(Number of fields can be more than 16)
Something like this:
Create Sequence id;
Create Table t1(id1 int4, last_name varchar(32), time_create_t1);
Create Table t2(id2 int4, first_name varchar(32), time_create_t2);
Create View name as
Select * from t1 a, t2 b where a.id1=b.id2;
Create Rule rl_ins_nameas on INSERT to name do instead nothing;
-- without it trigger is not allowed
Create Function fn_ins_name() returns opaque as
'
Declare
v_time_create timestamp;
Begin
v_tm_create:=current_timestamp;
Insert into t1(id1, last_name, time_create_t1)
values(nextval(''id''), new.last_name, v_time_create);
Insert into t2(id2, first_name, time_create_t2)
values(currval(''id''), new.first_name, v_time_create);
return null;
End;
' Language 'plpgsql';
Create Trigger tr_ins_name before INSERT on now_cis_user
for each row execute procedure fn_ins_name;
This example is very simple. The trigger is not fired.
Sorry for my English.
--
Thanks for everybody.
Acue.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can I create working trigger on view
@ 2002-10-18 05:27 Josh Berkus <josh@agliodbs.com>
parent: Акулов Александр <akulov@ien.ru>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-10-18 05:27 UTC (permalink / raw)
To: ?????? ?.?. <akulov@ien.ru>; pgsql-sql
Acue,
> Can I create working trigger on view?
>
> The problem is:
> I need a plpgsql function that execute on insert (or update,
> or delete) into view and knows the *OLD* and *NEW*.
> (Number of fields can be more than 16)
No. Create a RULE instead, which can be created on a view:
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/rules.html
BTW, you can increase the number of parameters accepted by functions by
re-compiling postgres. Also, 7.3 will have 32 as the default.
-Josh Berkus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can I create working trigger on view
@ 2002-10-18 13:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Акулов Александр <akulov@ien.ru>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-10-18 13:46 UTC (permalink / raw)
To: Акулов А.В. <akulov@ien.ru>; +Cc: pgsql-sql
"Acue" <akulov@ien.ru> writes:
> Can I create working trigger on view?
Not usefully. No tuple will ever actually be inserted into the view,
therefore the trigger will never fire.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-18 13:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-17 14:40 Can I create working trigger on view Акулов Александр <akulov@ien.ru>
2002-10-18 05:06 Can I create working trigger on view Акулов Александр <akulov@ien.ru>
2002-10-18 05:27 ` Josh Berkus <josh@agliodbs.com>
2002-10-18 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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