[pgAdmin4][patch] trigger's when clause related fixes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[pgAdmin4][patch] trigger's when clause related fixes

Zhang, Jie
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 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();
------------------------------------------------

[scenario 2]
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.

[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,

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!​




whenclause.patch (10K) Download Attachment
err.png (44K) Download Attachment
ok.png (44K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [pgAdmin4][patch] trigger's when clause related fixes

Akshay Joshi
Thanks, patch applied.

On Mon, Jun 29, 2020 at 2:15 PM Zhang, Jie <[hidden email]> 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 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();
------------------------------------------------

[scenario 2]
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.

[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,

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