Hi,
Please find the attached patch to fix RM #2933 - Add support for transition
tables in Postgres 10 triggers.
Thanks,
Khushboo
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/10_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/10_plus/create.sql
new file mode 100644
index 0000000..b26ab41
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/10_plus/create.sql
@@ -0,0 +1,36 @@
+{### Set a flag which allows us to put OR between events ###}
+{% set or_flag = False %}
+{% if data.lanname == 'edbspl' or data.tfunction == 'Inline EDB-SPL' %}
+CREATE OR REPLACE TRIGGER {{ conn|qtIdent(data.name) }}
+{% else %}
+CREATE{% if data.is_constraint_trigger %} CONSTRAINT{% endif %} TRIGGER {{ conn|qtIdent(data.name) }}
+{% endif %}
+ {{data.fires}} {% if data.evnt_insert %}INSERT{% set or_flag = True %}
+{% endif %}{% if data.evnt_delete %}
+{% if or_flag %} OR {% endif %}DELETE{% set or_flag = True %}
+{% endif %}{% if data.evnt_truncate %}
+{% if or_flag %} OR {% endif %}TRUNCATE{% set or_flag = True %}
+{% endif %}{% if data.evnt_update %}
+{% if or_flag %} OR {% endif %}UPDATE {% if data.columns|length > 0 %}OF {% for c in data.columns %}{% if loop.index != 1 %}, {% endif %}{{ conn|qtIdent(c) }}{% endfor %}{% endif %}
+{% endif %}
+
+ ON {{ conn|qtIdent(data.schema, data.table) }}
+{% if data.tgdeferrable %}
+ DEFERRABLE{% if data.tginitdeferred %} INITIALLY DEFERRED{% endif %}
+{% endif %}
+{% if data.tgoldtable or data.tgnewtable %}
+ REFERENCING{% if data.tgnewtable %} NEW TABLE AS {{ conn|qtIdent(data.tgnewtable) }}{% endif %}{% if data.tgoldtable %} OLD TABLE AS {{ conn|qtIdent(data.tgoldtable) }}{% endif %}
+
+{% endif %}
+ FOR EACH{% if data.is_row_trigger %} ROW{% else %} STATEMENT{% endif %}
+{% if data.whenclause %}
+
+ WHEN {{ data.whenclause }}{% endif %}
+
+ {% if data.prosrc is defined and
+ (data.lanname == 'edbspl' or data.tfunction == 'Inline EDB-SPL') %}{{ data.prosrc }}{% else %}EXECUTE PROCEDURE {{ data.tfunction }}{% if data.tgargs %}({{ data.tgargs }}){% else %}(){% endif%}{% endif%};
+
+{% if data.description %}
+COMMENT ON TRIGGER {{ conn|qtIdent(data.name) }} ON {{ conn|qtIdent(data.schema, data.table) }}
+ IS {{data.description|qtLiteral}};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/10_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/10_plus/properties.sql
new file mode 100644
index 0000000..1a7da0f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/10_plus/properties.sql
@@ -0,0 +1,25 @@
+SELECT t.oid,t.tgname AS name, t.xmin, 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'),
+ substring(pg_get_triggerdef(t.oid), 'WHEN (.*) \\$trigger')) AS whenclause,
+ -- We need to convert tgargs column bytea datatype to array datatype
+ (string_to_array(encode(tgargs, 'escape'), '\000')::text[])[1:tgnargs] AS custom_tgargs,
+{% if datlastsysoid %}
+ (CASE WHEN t.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_trigger,
+{% endif %}
+ (CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constarint,
+ (CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger,
+ tgoldtable,
+ tgnewtable
+FROM pg_trigger t
+ JOIN pg_class cl ON cl.oid=tgrelid
+ JOIN pg_namespace na ON na.oid=relnamespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
+ LEFT OUTER JOIN pg_proc p ON p.oid=t.tgfoid
+ LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
+WHERE NOT tgisinternal
+ AND tgrelid = {{tid}}::OID
+{% if trid %}
+ AND t.oid = {{trid}}::OID
+{% endif %}
+ORDER BY tgname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/js/trigger.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/js/trigger.js
index 843ff11..351682f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/js/trigger.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/triggers/static/js/trigger.js
@@ -467,6 +467,18 @@ define('pgadmin.node.trigger', [
return true;
}
},{
+ id: 'tgoldtable', label: gettext('Old table'),
+ type: 'text', group: gettext('Transition'),
+ cell: 'string', mode: ['create', 'edit', 'properties'],
+ deps: ['fires', 'is_constraint_trigger', 'evnt_insert', 'evnt_update', 'evnt_delete', 'columns'],
+ disabled: 'disableTransition'
+ },{
+ id: 'tgnewtable', label: gettext('New table'),
+ type: 'text', group: gettext('Transition'),
+ cell: 'string', mode: ['create', 'edit', 'properties'],
+ deps: ['fires', 'is_constraint_trigger', 'evnt_insert', 'evnt_update', 'evnt_delete', 'columns'],
+ disabled: 'disableTransition'
+ },{
id: 'prosrc', label: gettext('Code'), group: gettext('Code'),
type: 'text', mode: ['create', 'edit'], deps: ['tfunction'],
control: 'sql-field', visible: true,
@@ -575,6 +587,47 @@ define('pgadmin.node.trigger', [
}
}
return true;
+ },
+ // Disable/Enable Transition tables
+ disableTransition: function(m) {
+ var flag = true,
+ evnt = null,
+ name = this.name,
+ evnt_count = 0;
+
+ // Disable transition tables for view trigger and PG version < 100000
+ if(_.indexOf(Object.keys(m.node_info), 'table') == -1 ||
+ m.node_info.server.version < 100000) return true;
+
+ if (name == "tgoldtable") evnt = 'evnt_delete';
+ else if (name == "tgnewtable") evnt = 'evnt_insert';
+
+ if(m.get('evnt_insert')) evnt_count++;
+ if(m.get('evnt_update')) evnt_count++;
+ if(m.get('evnt_delete')) evnt_count++;
+
+
+ // Disable transition tables if
+ // - It is a constraint trigger
+ // - Fires other than AFTER
+ // - More than one events enabled
+ // - Update event with the column list
+
+ // Disable Old transition table if both UPDATE and DELETE events are disabled
+ // Disable New transition table if both UPDATE and INSERT events are disabled
+ if(!m.get('is_constraint_trigger') && m.get('fires') == 'AFTER' &&
+ (m.get('evnt_update') || m.get(evnt)) && evnt_count == 1) {
+ if (m.get('evnt_update') && (_.size(m.get('columns')) >= 1 && m.get('columns')[0] != "")) flag = true;
+ else flag = false;
+ }
+
+ flag && setTimeout(function() {
+ if(m.get(name)) {
+ m.set(name, null);
+ }
+ },10);
+
+ return flag;
}
}),
// Below function will enable right click menu for creating column
