Thread: Fixed PPAS trigger when condition
Hi Dave
There is one issue with PPAS while trigger creation as mentioned below:
create table a (a int not null primary key, b int);
create table b (a int not null primary key, b int);
--make trigger with when condition
create trigger tr_a after insert on a for each row
when (:new.a > 0)
begin
insert into b values (:new.*);
end;
After creating the trigger using above query, When condition is not visible in property window of that trigger. As per current implementation, it fetches the when part of trigger using below logic:
substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE')
this will not work with PPAS as we have not provided any procedure to be executed so it will return null. To fix this issue I have changed the above logic as below:
COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \\$trigger'))
Above logic has worked with PPAS and PG both. Attached is the patch file.
Please review it and if it looks good to you then please do commit the code
Regards,
Sanket Mehta
Sr Software engineer
Enterprisedb
Attachment
Thanks - patch applied and pushed. On Fri, Sep 19, 2014 at 7:41 AM, Sanket Mehta <sanket.mehta@enterprisedb.com> wrote: > Hi Dave > > There is one issue with PPAS while trigger creation as mentioned below: > > create table a (a int not null primary key, b int); > create table b (a int not null primary key, b int); > --make trigger with when condition > create trigger tr_a after insert on a for each row > when (:new.a > 0) > begin > insert into b values (:new.*); > end; > > After creating the trigger using above query, When condition is not visible > in property window of that trigger. As per current implementation, it > fetches the when part of trigger using below logic: > > substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE') > > this will not work with PPAS as we have not provided any procedure to be > executed so it will return null. To fix this issue I have changed the above > logic as below: > > COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), > substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \\$trigger')) > > Above logic has worked with PPAS and PG both. Attached is the patch file. > > Please review it and if it looks good to you then please do commit the code > > > Regards, > Sanket Mehta > Sr Software engineer > Enterprisedb > > > -- > Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company