"Create Script" for view assigns incorrect schema to trigger functions in "public" schema (RM #2381)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/2381 Originally created by Matt B at 2017-05-08 22:02:30 UTC.

View "myview" is in schema "a". It references a trigger function in schema "public".

Pgadmin4 in the SQL tab incorrectly shows the "CREATE TRIGGER" statement for "myview" as: "EXECUTE PROCEDURE a.mytriggerfunction();"

It should be blank to match the output of running "\d+ a.myview" from psql: "EXECUTE PROCEDURE mytriggerfunction();"

This could be broken for all trigger functions on views or tables where the trigger function is in the "public" schema and the view/table is in a non-public schema.

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-1 Originally created by Dave Page at 2017-05-09 07:48:51 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Fixed Version changed		1.5

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-2 Originally created by Khushboo Vashi at 2017-05-11 09:56:33 UTC.

Hi Matt,

Please mention the steps to reproduce this issue, as I can not reproduce this issue at my end.

Thanks, Khushboo

Redmine ticket header update:

Name	Old Value	New Value
Assigned To changed		Matt B

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-3 Originally created by Matt B at 2017-05-11 14:53:25 UTC.

Run these commands:

DROP VIEW IF EXISTS v.deleteme; DROP TABLE IF EXISTS public.deleteme; DROP TRIGGER IF EXISTS v_deletefunc ON v.deleteme; DROP FUNCTION IF EXISTS public.deletefunc();

CREATE SCHEMA v; CREATE TABLE deleteme ( col character varying(255) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default;

CREATE VIEW v.deleteme AS SELECT * FROM deleteme;

CREATE FUNCTION deletefunc() RETURNS trigger LANGUAGE 'plpgsql' COST 100.0 VOLATILE NOT LEAKPROOF AS $BODY$ BEGIN RETURN NULL; END; $BODY$;

CREATE TRIGGER v_deletefunc INSTEAD OF UPDATE ON v.deleteme FOR EACH ROW EXECUTE PROCEDURE deletefunc();

Now select the "deleteme" VIEW and look at the "SQL" pane output. The schema will be incorrect for the trigger showing "v.deletefunc()" instead of correct "public.deletefunc()" If you click "CREATE SCRIPT" for the "deleteme" view it will also be wrong there.

It is CORRECT if you click the "v_deletefunc" entry under "Triggers" for that view.

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-4 Originally created by Dave Page at 2017-05-16 14:50:51 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Fixed Version changed	1.5	1.6

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-5 Originally created by Dave Page at 2017-05-16 14:59:42 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Assigned To changed	Matt B	Khushboo Vashi

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-6 Originally created by Khushboo Vashi at 2017-06-05 09:06:34 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	New	In Progress

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-7 Originally created by Khushboo Vashi at 2017-06-05 10:27:18 UTC.

Patch submitted.

https://www.postgresql.org/message-id/CAFOhELf3boyf=V7_yhGkTv11y2nmZeitWS6ZG-jiJX9R69YEKQ@mail.gmail.com

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-8 Originally created by Khushboo Vashi at 2017-06-07 12:40:16 UTC.

Applied in changeset commit:b134ae2cb4d0a57833eb93e5fecece669214b16d.

Redmine ticket header update:

Name	Old Value	New Value
Status changed	In Progress	In Testing
Done Ratio changed	0	90

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-9 Originally created by Fahar Abbas at 2017-06-21 07:17:07 UTC.

Tested platform: Ubuntu 16.04 64 bit

Python 3

Commit ID:

commit bf5a2252af4589fc91a8c567fcbb216b0100e353

Redmine ticket header update:

Name	Old Value	New Value
Status changed	In Testing	Resolved

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/2381#note-10 Originally created by Fahar Abbas at 2018-01-09 05:37:11 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Done Ratio changed	90	100

[dpage]

Issue closed on Redmine.