Thread: [pgAdmin4][patch] trigger's when clause related fixes
Hi, all
[Incident]
'()' appears twice in trigger's when clause.
[scenario 1]
The step is as follow:
1) The SQL to create the trigger is as follows.
---------------------------------------------
CREATE TRIGGER trigger1
BEFORE UPDATE
ON public.tab1
FOR EACH ROW
WHEN (new.a IS NOT NULL)
EXECUTE PROCEDURE public.func();
---------------------------------------------
The step is as follow:
1) The SQL to create the trigger is as follows.
---------------------------------------------
CREATE TRIGGER trigger1
BEFORE UPDATE
ON public.tab1
FOR EACH ROW
WHEN (new.a IS NOT NULL)
EXECUTE PROCEDURE public.func();
---------------------------------------------
2) Right click trigger1
3) Click CREATE Script
3) Click CREATE Script
The SQL shown in 'Query Editor' is as follows.
---------------------------------------------
CREATE TRIGGER trigger1
BEFORE UPDATE
ON public.tab1
FOR EACH ROW
WHEN ((new.a IS NOT NULL)) # () appears twice.
EXECUTE PROCEDURE public.func();
------------------------------------------------
---------------------------------------------
CREATE TRIGGER trigger1
BEFORE UPDATE
ON public.tab1
FOR EACH ROW
WHEN ((new.a IS NOT NULL)) # () appears twice.
EXECUTE PROCEDURE public.func();
------------------------------------------------
[scenario 2]
1) Right click trigger1
2) Click Properties...
3) Click Event tab
1) Right click trigger1
2) Click Properties...
3) Click Event tab
The SQL shown in 'When' is as follows.
((new.a IS NOT NULL)) # () appears twice.
((new.a IS NOT NULL)) # () appears twice.
[The detail of cause]
pg_get_triggerdef(trigger_oid, pretty_bool) get CREATE [ CONSTRAINT ] TRIGGER command for trigger
Parameter true missing in pg_get_triggerdef function.
SELECT t.oid,t.tgname AS name, t.xmin, t.tgenabled AS is_enable_trigger, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \$trigger')) AS whenclause,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \$trigger')) AS whenclause,
pgadmin4\web\pgadmin\browser\server_groups\servers\databases\schemas\tables\templates\triggers\sql\pg\10_plus\properties.sql
[The summary of correction]
pg_get_triggerdef(t.oid) => pg_get_triggerdef(t.oid, true)
Here is a patch for trigger's when clause.
Please review.
Please review.
Best Regards!
Attachment
Thanks, patch applied.
On Mon, Jun 29, 2020 at 2:15 PM Zhang, Jie <zhangjie2@cn.fujitsu.com> wrote:
Hi, all[Incident]'()' appears twice in trigger's when clause.[scenario 1]
The step is as follow:
1) The SQL to create the trigger is as follows.
---------------------------------------------
CREATE TRIGGER trigger1
BEFORE UPDATE
ON public.tab1
FOR EACH ROW
WHEN (new.a IS NOT NULL)
EXECUTE PROCEDURE public.func();
---------------------------------------------2) Right click trigger1
3) Click CREATE ScriptThe SQL shown in 'Query Editor' is as follows.
---------------------------------------------
CREATE TRIGGER trigger1
BEFORE UPDATE
ON public.tab1
FOR EACH ROW
WHEN ((new.a IS NOT NULL)) # () appears twice.
EXECUTE PROCEDURE public.func();
------------------------------------------------[scenario 2]
1) Right click trigger1
2) Click Properties...
3) Click Event tabThe SQL shown in 'When' is as follows.
((new.a IS NOT NULL)) # () appears twice.
[The detail of cause]pg_get_triggerdef(trigger_oid, pretty_bool) get CREATE [ CONSTRAINT ] TRIGGER command for triggerParameter true missing in pg_get_triggerdef function.SELECT t.oid,t.tgname AS name, t.xmin, t.tgenabled AS is_enable_trigger, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
COALESCE(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE (PROCEDURE|FUNCTION)'),
substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \$trigger')) AS whenclause,pgadmin4\web\pgadmin\browser\server_groups\servers\databases\schemas\tables\templates\triggers\sql\pg\10_plus\properties.sql
[The summary of correction]
pg_get_triggerdef(t.oid) => pg_get_triggerdef(t.oid, true)Here is a patch for trigger's when clause.
Please review.Best Regards!
Thanks & Regards
Akshay Joshi
Sr. Software Architect
EnterpriseDB Software India Private Limited
Mobile: +91 976-788-8246