Hi,
Please ignore my previous patch, find the attached updated one.
Thanks,
Khushboo
On Wed, Jun 6, 2018 at 12:12 PM, Khushboo Vashi <
khushboo.va...@enterprisedb.com> wrote:
> Hi,
>
> Please find the attached patch to fix #3362 - Fix the functions for PG
> v11, and add support procedure for PG v11.
>
> Thanks,
> Khushboo
>
>
diff --git a/docs/en_US/procedure_dialog.rst b/docs/en_US/procedure_dialog.rst
index da5f34f..82c2227 100644
--- a/docs/en_US/procedure_dialog.rst
+++ b/docs/en_US/procedure_dialog.rst
@@ -4,7 +4,7 @@
The Procedure Dialog
********************
-Use the *Procedure* dialog to create a procedure; procedures are supported by EDB Postgres Advanced Server. The *Procedure* dialog allows you to implement options of the CREATE PROCEDURE command; for more information about the CREATE PROCEDURE SQL command, please see the Database Compatibility for Oracle Developer's, available at:
+Use the *Procedure* dialog to create a procedure; procedures are supported by PostgreSQL v11 and EDB Postgres Advanced Server. The *Procedure* dialog allows you to implement options of the CREATE PROCEDURE command; for more information about the CREATE PROCEDURE SQL command, please see the Database Compatibility for Oracle Developer's, available at:
http://www.enterprisedb.com
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
index d8e7a68..7fecade 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
@@ -1592,10 +1592,11 @@ class ProcedureModule(SchemaChildModule):
"""
super(ProcedureModule, self).__init__(*args, **kwargs)
- self.min_ver = 90100
+ self.min_ver = 110000
self.max_ver = None
+ self.min_ppasver = 90100
self.min_gpdbver = 1000000000
- self.server_type = ['ppas']
+ self.server_type = ['pg', 'ppas']
def get_nodes(self, gid, sid, did, scid):
"""
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/static/js/procedure.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/static/js/procedure.js
index 041b509..1e02c99 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/static/js/procedure.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/static/js/procedure.js
@@ -71,11 +71,7 @@ define('pgadmin.node.procedure', [
if ('catalog' in node_hierarchy)
return false;
- // Procedures supported only in PPAS
- return (
- 'server' in node_hierarchy &&
- node_hierarchy['server'].server_type == 'ppas'
- );
+ return true;
},
model: Function.model.extend({
defaults: _.extend({},
@@ -103,20 +99,24 @@ define('pgadmin.node.procedure', [
return true;
}
switch(this.name){
- case 'provolatility':
+ case 'provolatile':
case 'proisstrict':
- case 'prosecdef':
case 'procost':
case 'proleakproof':
case 'variables':
- return this.node_info.server.version < 90500;
+ return (this.node_info.server.version < 90500 || this.node_info.server.type != 'ppas');
+ case 'prosecdef':
+ return (this.node_info.server.version < 90500);
case 'prorows':
var server = this.node_info.server;
return !(server.version >= 90500 && m.get('proretset') == true);
case 'funcowner':
- case 'lanname':
case 'proargs':
return true;
+ case 'proparallel':
+ return (this.node_info.server.version < 90600 || this.node_info.server.type != 'ppas');
+ case 'lanname':
+ return this.node_info.server.version < 110000;
default:
return false;
}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/coll_stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/coll_stats.sql
new file mode 100644
index 0000000..cc4f734
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.prokind IN ('f', 'w')
+ AND typname NOT IN ('trigger', 'event_trigger')
+ )
+ORDER BY funcname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/create.sql
new file mode 100644
index 0000000..01cdf51
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/create.sql
@@ -0,0 +1,70 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}
+{% set is_columns = [] %}
+{% if data %}
+{% if query_for == 'sql_panel' and func_def is defined %}
+CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}}
+{% else %}
+CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.arguments %}
+{% for p in data.arguments %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname) }} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %}
+{% if not loop.last %}, {% endif %}
+{% endfor %}
+{% endif -%}
+)
+{% endif %}
+ RETURNS{% if data.proretset and (data.prorettypename.startswith('SETOF ') or data.prorettypename.startswith('TABLE')) %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %}
+
+ LANGUAGE {{ data.lanname|qtLiteral }}
+{% if data.procost %}
+
+ COST {{data.procost}}
+{% endif %}
+ {% if data.provolatile %}{% if data.provolatile == 'i' %}IMMUTABLE{% elif data.provolatile == 's' %}STABLE{% else %}VOLATILE{% endif %} {% endif %}{% if data.proleakproof %}LEAKPROOF {% endif %}
+{% if data.proisstrict %}STRICT {% endif %}
+{% if data.prosecdef %}SECURITY DEFINER {% endif %}
+{% if data.proiswindow %}WINDOW {% endif %}
+{% if data.proparallel and (data.proparallel == 'r' or data.proparallel == 's') %}
+{% if data.proparallel == 'r' %}PARALLEL RESTRICTED{% elif data.proparallel == 's' %}PARALLEL SAFE{% endif %}{% endif -%}
+{% if data.prorows and (data.prorows | int) > 0 %}
+
+ ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor %}
+{% endif %}
+
+AS {% if data.lanname == 'c' %}
+{{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }}
+{% else %}
+$BODY$
+{{ data.prosrc }}
+$BODY${% endif -%};
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({{data.func_args_without}})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif -%}
+{% if data.acl %}
+{% for p in data.acl %}
+
+{{ PRIVILEGE.SET(conn, "FUNCTION", p.grantee, data.name, p.without_grant, p.with_grant, data.pronamespace, data.func_args_without)}}
+{% endfor %}{% endif %}
+{% if data.revoke_all %}
+
+{{ PRIVILEGE.UNSETALL(conn, "FUNCTION", "PUBLIC", data.name, data.pronamespace, data.func_args_without)}}
+{% endif %}
+{% if data.description %}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({{data.func_args_without}})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', data.name, r.provider, r.label, data.pronamespace, data.func_args_without) }}
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/delete.sql
new file mode 100644
index 0000000..540044c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/delete.sql
@@ -0,0 +1,21 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP FUNCTION {{ conn|qtIdent(nspname, name) }}{{func_args}}{% if cascade %} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/get_definition.sql
new file mode 100644
index 0000000..ab25dbf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') as
+ func_with_identity_arguments,
+ nspname,
+ pr.proname as proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') as func_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND pr.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/get_oid.sql
new file mode 100644
index 0000000..93a2107
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/get_oid.sql
@@ -0,0 +1,17 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace as nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/node.sql
new file mode 100644
index 0000000..5dae8d1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/node.sql
@@ -0,0 +1,22 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/properties.sql
new file mode 100644
index 0000000..50c2ca0
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/properties.sql
@@ -0,0 +1,33 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes) AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ CASE WHEN pr.prokind = 'w' THEN TRUE ELSE FALSE END AS proiswindow,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=pr.oid) AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/update.sql
new file mode 100644
index 0000000..92bd24b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/11_plus/update.sql
@@ -0,0 +1,116 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, o_data.name) }}({{
+o_data.proargtypenames }})
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+{% if data.change_func %}
+
+CREATE OR REPLACE FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({% if data.arguments %}
+{% for p in data.arguments %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname) }} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %}
+{% if not loop.last %},{% endif %}
+{% endfor %}
+{% endif -%}
+)
+ RETURNS {{ o_data.prorettypename }}
+{% if 'lanname' in data %}
+ LANGUAGE {{ data.lanname|qtLiteral }} {% else %}
+ LANGUAGE {{ o_data.lanname|qtLiteral }}
+ {% endif %}{% if 'provolatile' in data and data.provolatile %}{{ data.provolatile }} {% elif 'provolatile' not in data and o_data.provolatile %}{{ o_data.provolatile }}{% endif %}
+{% if ('proleakproof' in data and data.proleakproof) or ('proleakproof' not in data and o_data.proleakproof) %} LEAKPROOF{% elif 'proleakproof' in data and not data.proleakproof %} NOT LEAKPROOF{% endif %}
+{% if ('proisstrict' in data and data.proisstrict) or ('proisstrict' not in data and o_data.proisstrict) %} STRICT{% endif %}
+{% if ('prosecdef' in data and data.prosecdef) or ('prosecdef' not in data and o_data.prosecdef) %} SECURITY DEFINER{% endif %}
+{% if ('proiswindow' in data and data.proiswindow) or ('proiswindow' not in data and o_data.proiswindow) %} WINDOW{% endif %}
+
+ {% if 'proparallel' in data and data.proparallel %}PARALLEL {{ data.proparallel }}{% elif 'proparallel' not in data and o_data.proparallel %}PARALLEL {{ o_data.proparallel }}{% endif %}
+
+ {% if data.procost %}COST {{data.procost}}{% elif o_data.procost %}COST {{o_data.procost}}{% endif %}{% if data.prorows %}
+
+ ROWS {{data.prorows}}{% elif data.prorows is not defined and o_data.prorows and o_data.prorows != '0' %} ROWS {{o_data.prorows}} {%endif -%}{% if data.merged_variables %}{% for v in data.merged_variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%}
+ {% endif %}
+
+AS {% if 'probin' in data or 'prosrc_c' in data %}
+{% if 'probin' in data %}{{ data.probin|qtLiteral }}{% else %}{{ o_data.probin|qtLiteral }}{% endif %}, {% if 'prosrc_c' in data %}{{ data.prosrc_c|qtLiteral }}{% else %}{{ o_data.prosrc_c|qtLiteral }}{% endif %}{% elif 'prosrc' in data %}
+$BODY${{ data.prosrc }}$BODY${% elif o_data.lanname == 'c' %}
+{{ o_data.probin|qtLiteral }}, {{ o_data.prosrc_c|qtLiteral }}{% else %}
+$BODY${{ o_data.prosrc }}$BODY${% endif -%};
+{% endif -%}
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif -%}
+{# The SQL generated below will change priviledges #}
+{% if data.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in data.datacl %}
+{% for priv in data.acl.changed %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}{% endif -%}
+{% endif -%}
+{% if data.change_func == False %}
+{% if data.variables %}
+{% if 'deleted' in data.variables and data.variables.deleted|length > 0 %}
+
+{{ VARIABLE.UNSET(conn, 'FUNCTION', name, data.variables.deleted, o_data.pronamespace, o_data.proargtypenames) }}
+{% endif -%}
+{% if 'merged_variables' in data and data.merged_variables|length > 0 %}
+
+{{ VARIABLE.SET(conn, 'FUNCTION', name, data.merged_variables, o_data.pronamespace, o_data.proargtypenames) }}
+{% endif -%}
+{% endif -%}
+{% endif -%}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+
+{{ SECLABEL.UNSET(conn, 'FUNCTION', name, r.provider, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if data.description is defined and data.description != o_data.description%}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.pronamespace %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ SET SCHEMA {{ conn|qtIdent(data.pronamespace) }};
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/acl.sql
new file mode 100644
index 0000000..2e7aae5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/acl.sql
@@ -0,0 +1,35 @@
+SELECT
+ COALESCE(gt.rolname, 'PUBLIC') AS grantee,
+ g.rolname AS grantor, array_agg(privilege_type) AS privileges,
+ array_agg(is_grantable) AS grantable
+FROM
+ (SELECT
+ d.grantee, d.grantor, d.is_grantable,
+ CASE d.privilege_type
+ WHEN 'CONNECT' THEN 'c'
+ WHEN 'CREATE' THEN 'C'
+ WHEN 'DELETE' THEN 'd'
+ WHEN 'EXECUTE' THEN 'X'
+ WHEN 'INSERT' THEN 'a'
+ WHEN 'REFERENCES' THEN 'x'
+ WHEN 'SELECT' THEN 'r'
+ WHEN 'TEMPORARY' THEN 'T'
+ WHEN 'TRIGGER' THEN 't'
+ WHEN 'TRUNCATE' THEN 'D'
+ WHEN 'UPDATE' THEN 'w'
+ WHEN 'USAGE' THEN 'U'
+ ELSE 'UNKNOWN'
+ END AS privilege_type
+ FROM
+ (SELECT
+ (d).grantee AS grantee, (d).grantor AS grantor,
+ (d).is_grantable AS is_grantable,
+ (d).privilege_type AS privilege_type
+ FROM
+ (SELECT aclexplode(db.proacl) AS d FROM pg_proc db
+ WHERE db.oid = {{fnid}}::OID) a
+ ) d
+ ) d
+ LEFT JOIN pg_catalog.pg_roles g ON (d.grantor = g.oid)
+ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
+GROUP BY g.rolname, gt.rolname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/coll_stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/coll_stats.sql
new file mode 100644
index 0000000..5924679
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.prokind = 'p'::char
+ AND typname NOT IN ('trigger', 'event_trigger')
+ )
+ORDER BY funcname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/create.sql
new file mode 100644
index 0000000..ffff17d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/create.sql
@@ -0,0 +1,46 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}
+{% set is_columns = [] %}
+{% if data %}
+{% if query_for == 'sql_panel' and func_def is defined %}
+CREATE OR REPLACE PROCEDURE {{func_def}}
+{% else %}
+CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.arguments is defined %}
+({% for p in data.arguments %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %}
+{% if not loop.last %}, {% endif %}
+{% endfor -%}
+{% endif %}
+)
+{% endif %}
+LANGUAGE {{ data.lanname|qtLiteral }}
+{% if data.prosecdef %}SECURITY DEFINER {% endif %}
+
+AS {% if data.lanname == 'c' %}
+{{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }}
+{% else %}
+$BODY${{ data.prosrc }}$BODY${% endif -%};
+{% if data.acl and not is_sql %}
+{% for p in data.acl %}
+
+{{ PRIVILEGE.SET(conn, "PROCEDURE", p.grantee, data.name, p.without_grant, p.with_grant, data.pronamespace, data.func_args_without)}}
+{% endfor %}{% endif %}
+{% if data.revoke_all %}
+
+{{ PRIVILEGE.UNSETALL(conn, "PROCEDURE", "PUBLIC", data.name, data.pronamespace, data.func_args_without)}}
+{% endif %}
+{% if data.description %}
+
+COMMENT ON PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+
+{{ SECLABEL.SET(conn, 'PROCEDURE', data.name, r.provider, r.label, data.pronamespace, data.func_args_without) }}
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/delete.sql
new file mode 100644
index 0000000..b4fcdc2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/delete.sql
@@ -0,0 +1,21 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind = 'p'::char
+ AND pronamespace = {{scid}}::oid
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP PROCEDURE {{ conn|qtIdent(nspname, name) }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_definition.sql
new file mode 100644
index 0000000..4c2e9d9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') as
+ func_with_identity_arguments,
+ nspname,
+ pr.proname as proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') as func_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind = 'p'::char
+ AND pronamespace = {{scid}}::oid
+ AND pr.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_languages.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_languages.sql
new file mode 100644
index 0000000..f81ddfb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_languages.sql
@@ -0,0 +1,4 @@
+SELECT
+ lanname as label, lanname as value
+FROM
+ pg_language;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_oid.sql
new file mode 100644
index 0000000..be98b53
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_oid.sql
@@ -0,0 +1,17 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace AS nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ pr.prokind = 'p'::char
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_out_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_out_types.sql
new file mode 100644
index 0000000..64a1187
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_out_types.sql
@@ -0,0 +1,6 @@
+SELECT
+ format_type(oid, NULL) AS out_arg_type
+FROM
+ pg_type
+WHERE
+ oid = {{ out_arg_oid }}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_schema.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_schema.sql
new file mode 100644
index 0000000..127d4b9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_schema.sql
@@ -0,0 +1,6 @@
+SELECT
+ nspname
+FROM
+ pg_namespace
+WHERE
+ oid = {{ scid }}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_types.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_types.sql
new file mode 100644
index 0000000..2a5582e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/get_types.sql
@@ -0,0 +1,20 @@
+SELECT
+ *
+FROM
+ (SELECT
+ format_type(t.oid,NULL) AS typname,
+ CASE WHEN typelem > 0 THEN typelem ELSE t.oid END as elemoid, typlen, typtype, t.oid, nspname,
+ (SELECT COUNT(1) FROM pg_type t2 WHERE t2.typname = t.typname) > 1 AS isdup
+ FROM
+ pg_type t
+ JOIN
+ pg_namespace nsp ON typnamespace=nsp.oid
+ WHERE
+ (NOT (typname = 'unknown' AND nspname = 'pg_catalog'))
+ AND
+ (
+ typtype IN ('b', 'c', 'd', 'e', 'p', 'r')
+ AND typname NOT IN ('any', 'trigger', 'language_handler', 'event_trigger')
+ )
+ ) AS dummy
+ORDER BY nspname <> 'pg_catalog', nspname <> 'public', nspname, 1;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/node.sql
new file mode 100644
index 0000000..89dc768
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/node.sql
@@ -0,0 +1,29 @@
+SELECT
+ pr.oid,
+ CASE WHEN
+ pg_catalog.pg_get_function_identity_arguments(pr.oid) <> ''
+ THEN
+ pr.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')'
+ ELSE
+ pr.proname
+ END AS name,
+ lanname, pg_get_userbyid(proowner) AS funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind = 'p'::char
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/properties.sql
new file mode 100644
index 0000000..79bea86
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/properties.sql
@@ -0,0 +1,39 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes) AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (CASE WHEN
+ pg_catalog.pg_get_function_identity_arguments(pr.oid) <> ''
+ THEN
+ pr.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')'
+ ELSE
+ pr.proname
+ END) AS name_with_args,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=pr.oid) AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind = 'p'
+ AND typname NOT IN ('trigger', 'event_trigger')
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/stats.sql
new file mode 100644
index 0000000..2e276b6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/stats.sql
@@ -0,0 +1,8 @@
+SELECT
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ funcid = {{fnid}}::OID
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/update.sql
new file mode 100644
index 0000000..38cd413
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/update.sql
@@ -0,0 +1,90 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER PROCEDURE {{ conn|qtIdent(o_data.pronamespace, o_data.name) }}{% if o_data.proargtypenames %}({{ o_data.proargtypenames }}){% endif %}
+
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+
+{% endif -%}
+{% if data.change_func %}
+CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(o_data.pronamespace, name) }}({% if data.arguments %}{% for p in data.arguments %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname) }} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %}
+{% if not loop.last %}, {% endif %}
+{% endfor %}
+{% endif %}
+)
+{% if 'lanname' in data %}
+ LANGUAGE {{ data.lanname|qtLiteral }} {% else %}
+ LANGUAGE {{ o_data.lanname|qtLiteral }}
+ {% endif %}
+{% if ('prosecdef' in data and data.prosecdef) or ('prosecdef' not in data and o_data.prosecdef) %}SECURITY DEFINER{% endif %}
+
+AS {% if 'probin' in data or 'prosrc_c' in data %}
+{% if 'probin' in data %}{{ data.probin|qtLiteral }}{% else %}{{ o_data.probin|qtLiteral }}{% endif %}, {% if 'prosrc_c' in data %}{{ data.prosrc_c|qtLiteral }}{% else %}{{ o_data.prosrc_c|qtLiteral }}{% endif %}{% elif 'prosrc' in data %}
+$BODY${{ data.prosrc }}$BODY${% elif o_data.lanname == 'c' %}
+{{ o_data.probin|qtLiteral }}, {{ o_data.prosrc_c|qtLiteral }}{% else %}
+$BODY${{ o_data.prosrc }}$BODY${% endif -%};
+{% endif -%}
+{% if data.funcowner %}
+
+ALTER PROCEDURE {{ conn|qtIdent(o_data.pronamespace, name) }}{% if o_data.proargtypenames %}({{ o_data.proargtypenames }}){% endif %}
+ OWNER TO {{ data.funcowner }};
+{% endif -%}
+{# The SQL generated below will change priviledges #}
+{% if data.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'PROCEDURE', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in data.datacl %}
+{% for priv in data.acl.changed %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'PROCEDURE', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+
+{{ PRIVILEGE.SET(conn, 'PROCEDURE', priv.grantee, name, priv.without_grant,
+ priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'PROCEDURE', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif %}
+{% endif -%}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+
+{{ SECLABEL.UNSET(conn, 'PROCEDURE', name, r.provider, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABEL.SET(conn, 'PROCEDURE', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABEL.SET(conn, 'PROCEDURE', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if data.description is defined and data.description != o_data.description%}
+
+COMMENT ON PROCEDURE {{ conn|qtIdent(o_data.pronamespace, name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.pronamespace %}
+
+ALTER PROCEDURE {{ conn|qtIdent(o_data.pronamespace, name) }}
+ SET SCHEMA {{ conn|qtIdent(data.pronamespace) }};
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/variables.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/variables.sql
new file mode 100644
index 0000000..5233c71
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/variables.sql
@@ -0,0 +1,6 @@
+SELECT
+ name, vartype, min_val, max_val, enumvals
+FROM
+ pg_settings
+WHERE
+ context in ('user', 'superuser');
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/coll_stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/coll_stats.sql
new file mode 100644
index 0000000..cd8cecd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.prokind IN ('f', 'w')
+ AND typname = 'trigger'
+ )
+ORDER BY funcname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/create.sql
new file mode 100644
index 0000000..20499a9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/create.sql
@@ -0,0 +1,57 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}
+{% set is_columns = [] %}
+{% if data %}
+CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.proargnames %}{{data.proargnames}}{% endif %})
+ RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }}
+ LANGUAGE {{ data.lanname|qtLiteral }}
+{% if data.procost %}
+ COST {{data.procost}}
+{% endif %}
+ {% if data.provolatile %}{% if data.provolatile == 'i' %}IMMUTABLE{% elif data.provolatile == 's' %}STABLE{% else %}VOLATILE{% endif %} {% endif %}{% if data.proleakproof %}LEAKPROOF {% else %}NOT LEAKPROOF {% endif %}
+{% if data.proisstrict %}STRICT {% endif %}
+{% if data.prosecdef %}SECURITY DEFINER {% endif %}
+{% if data.proiswindow %}WINDOW{% endif %}
+{% if data.prorows and (data.prorows | int) > 0 %}
+
+ ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor %}
+{% endif %}
+
+AS {% if data.lanname == 'c' %}
+{{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }}
+{% else %}
+$BODY$
+{{ data.prosrc }}
+$BODY${% endif -%};
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({{data.func_args}})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif -%}
+{% if data.acl %}
+{% for p in data.acl %}
+
+{{ PRIVILEGE.SET(conn, "FUNCTION", p.grantee, data.name, p.without_grant, p.with_grant, data.pronamespace, data.func_args)}}
+{% endfor %}{% endif %}
+{% if data.revoke_all %}
+
+{{ PRIVILEGE.UNSETALL(conn, "FUNCTION", "PUBLIC", data.name, data.pronamespace, data.func_args_without)}}
+{% endif %}
+{% if data.description %}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({{data.func_args}})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+{% if data.seclabels %}
+{% for r in data.seclabels %}
+{% if r.label and r.provider %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', data.name, r.provider, r.label, data.pronamespace, data.func_args) }}
+{% endif %}
+{% endfor %}
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/delete.sql
new file mode 100644
index 0000000..f7f906b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/delete.sql
@@ -0,0 +1,21 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND typname IN ('trigger', 'event_trigger')
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP FUNCTION {{ conn|qtIdent(nspname, name) }}{{func_args}}{% if cascade %} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/get_definition.sql
new file mode 100644
index 0000000..ab25dbf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') as
+ func_with_identity_arguments,
+ nspname,
+ pr.proname as proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') as func_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND pr.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/get_oid.sql
new file mode 100644
index 0000000..413df91
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/get_oid.sql
@@ -0,0 +1,18 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace AS nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname IN ('trigger', 'event_trigger')
+ AND lanname NOT IN ('edbspl', 'sql', 'internal')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/node.sql
new file mode 100644
index 0000000..55d7c54
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/node.sql
@@ -0,0 +1,23 @@
+SELECT
+ pr.oid, pr.proname || '()' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname IN ('trigger', 'event_trigger')
+ AND lanname NOT IN ('edbspl', 'sql', 'internal')
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/properties.sql
new file mode 100644
index 0000000..09d7866
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/properties.sql
@@ -0,0 +1,33 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes) AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=pr.oid) AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname IN ('trigger', 'event_trigger')
+ AND lanname NOT IN ('edbspl', 'sql', 'internal')
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/update.sql
new file mode 100644
index 0000000..413be62
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/11_plus/update.sql
@@ -0,0 +1,109 @@
+{% import 'macros/functions/security.macros' as SECLABEL %}
+{% import 'macros/functions/privilege.macros' as PRIVILEGE %}
+{% import 'macros/functions/variable.macros' as VARIABLE %}{% if data %}
+{% set name = o_data.name %}
+{% if data.name %}
+{% if data.name != o_data.name %}
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, o_data.name) }}({{
+o_data.proargtypenames }})
+ RENAME TO {{ conn|qtIdent(data.name) }};
+{% set name = data.name %}
+{% endif %}
+{% endif -%}
+{% if data.change_func %}
+
+CREATE OR REPLACE FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}()
+ RETURNS {{ o_data.prorettypename }}
+{% if 'lanname' in data %}
+ LANGUAGE {{ data.lanname|qtLiteral }} {% else %}
+ LANGUAGE {{ o_data.lanname|qtLiteral }}
+ {% endif %}{% if 'provolatile' in data and data.provolatile %}{{ data.provolatile }} {% elif 'provolatile' not in data and o_data.provolatile %}{{ o_data.provolatile }}{% endif %}
+{% if ('proleakproof' in data and data.proleakproof) or ('proleakproof' not in data and o_data.proleakproof) %} LEAKPROOF{% elif 'proleakproof' in data and not data.proleakproof %} NOT LEAKPROOF{% endif %}
+{% if ('proisstrict' in data and data.proisstrict) or ('proisstrict' not in data and o_data.proisstrict) %} STRICT{% endif %}
+{% if ('prosecdef' in data and data.prosecdef) or ('prosecdef' not in data and o_data.prosecdef) %} SECURITY DEFINER{% endif %}
+{% if ('proiswindow' in data and data.proiswindow) or ('proiswindow' not in data and o_data.proiswindow) %} WINDOW{% endif %}
+
+ {% if data.procost %}COST {{data.procost}}{% elif o_data.procost %}COST {{o_data.procost}}{% endif %}{% if data.prorows %}
+
+ ROWS {{data.prorows}}{% elif data.prorows is not defined and o_data.prorows and o_data.prorows != '0' %} ROWS {{o_data.prorows}} {%endif -%}{% if data.merged_variables %}{% for v in data.merged_variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%}
+ {% endif %}
+
+AS {% if 'probin' in data or 'prosrc_c' in data %}
+{% if 'probin' in data %}{{ data.probin|qtLiteral }}{% else %}{{ o_data.probin|qtLiteral }}{% endif %}, {% if 'prosrc_c' in data %}{{ data.prosrc_c|qtLiteral }}{% else %}{{ o_data.prosrc_c|qtLiteral }}{% endif %}{% elif 'prosrc' in data %}
+$BODY${{ data.prosrc }}$BODY${% elif o_data.lanname == 'c' %}
+{{ o_data.probin|qtLiteral }}, {{ o_data.prosrc_c|qtLiteral }}{% else %}
+$BODY${{ o_data.prosrc }}$BODY${% endif -%};
+{% endif -%}
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif -%}
+{# The SQL generated below will change priviledges #}
+{% if data.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in data.datacl %}
+{% for priv in data.acl.changed %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}{% endif -%}
+{% endif -%}
+{% if data.change_func == False %}
+{% if data.variables %}
+{% if 'deleted' in data.variables and data.variables.deleted|length > 0 %}
+
+{{ VARIABLE.UNSET(conn, 'FUNCTION', name, data.variables.deleted, o_data.pronamespace, o_data.proargtypenames) }}
+{% endif -%}
+{% if 'merged_variables' in data and data.merged_variables|length > 0 %}
+
+{{ VARIABLE.SET(conn, 'FUNCTION', name, data.merged_variables, o_data.pronamespace, o_data.proargtypenames) }}
+{% endif -%}
+{% endif -%}
+{% endif -%}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+
+{{ SECLABEL.UNSET(conn, 'FUNCTION', name, r.provider, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if data.description is defined and data.description != o_data.description%}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.pronamespace %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ SET SCHEMA {{ conn|qtIdent(data.pronamespace) }};
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_add.py
new file mode 100644
index 0000000..71ee285
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_add.py
@@ -0,0 +1,80 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+import uuid
+
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class FunctionAddTestCase(BaseTestGenerator):
+ """ This class will add new function under schema node. """
+ scenarios = [
+ # Fetching default URL for function node.
+ ('Fetch Function Node URL', dict(
+ url='/browser/function/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will add function under schema node. """
+ super(FunctionAddTestCase, self).runTest()
+ self = funcs_utils.set_up(self)
+ db_user = self.server["username"]
+ data = {
+ "acl": [
+ {
+ "grantee": db_user,
+ "grantor": db_user,
+ "privileges":
+ [
+ {
+ "privilege_type": "X",
+ "privilege": True,
+ "with_grant": True
+ }
+ ]
+ }
+ ],
+ "arguments": [],
+ "funcowner": db_user,
+ "lanname": "sql",
+ "name": "test_function",
+ "options": [],
+ "proleakproof": True,
+ "pronamespace": 2200,
+ "prorettypename": "integer",
+ "prosecdef": True,
+ "prosrc": "SELECT 1;",
+ "probin": "$libdir/",
+ "provolatile": "s",
+ "seclabels": [],
+ "variables": []
+ }
+
+ data["name"] = "test_function_add_%s" % str(uuid.uuid4())[1:8]
+ if self.schema_id:
+ data['pronamespace'] = self.schema_id
+ else:
+ self.schema_id = data['pronamespace']
+ response = self.tester.post(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' + str(self.db_id) +
+ '/' + str(self.schema_id) + '/',
+ data=json.dumps(data),
+ content_type='html/json'
+ )
+
+ self.assertEquals(response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_delete.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_delete.py
new file mode 100644
index 0000000..79a89fa
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_delete.py
@@ -0,0 +1,49 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class FunctionDeleteTestCase(BaseTestGenerator):
+ """ This class will delete the function under schema node. """
+ scenarios = [
+ # Fetching default URL for function node.
+ ('Fetch Function Node URL',
+ dict(url='/browser/function/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will delete function under database node. """
+ super(FunctionDeleteTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ func_name = "test_function_delete_%s" % str(uuid.uuid4())[1:8]
+ function_info = funcs_utils.create_function(
+ self.server, self.db_name, self.schema_name, func_name)
+
+ func_id = function_info[0]
+ response = self.tester.delete(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' +
+ str(self.db_id) + '/' +
+ str(self.schema_id) + '/' + str(func_id),
+ content_type='html/json'
+ )
+ self.assertEquals(response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_get.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_get.py
new file mode 100644
index 0000000..32a5585
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_get.py
@@ -0,0 +1,49 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class FunctionGetTestCase(BaseTestGenerator):
+ """This class will fetch added function under schema node."""
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching default URL for function node.
+ ('Fetch Function Node URL',
+ dict(url='/browser/function/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will delete function under database node. """
+ super(FunctionGetTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ func_name = "test_function_get_%s" % str(uuid.uuid4())[1:8]
+ function_info = funcs_utils.create_function(
+ self.server, self.db_name, self.schema_name, func_name)
+
+ trigger_func_id = function_info[0]
+ response = self.tester.get(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' +
+ str(self.db_id) + '/' +
+ str(self.schema_id) + '/' + str(trigger_func_id),
+ content_type='html/json')
+ self.assertEquals(response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_put.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_put.py
new file mode 100644
index 0000000..8565687
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_function_put.py
@@ -0,0 +1,57 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class FunctionPutTestCase(BaseTestGenerator):
+ """ This class will update new function under schema node. """
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching default URL for function node.
+ ('Fetch Function Node URL',
+ dict(url='/browser/function/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will update function under database node. """
+ super(FunctionPutTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ func_name = "test_event_delete_%s" % str(uuid.uuid4())[1:8]
+ function_info = funcs_utils.create_function(
+ self.server, self.db_name, self.schema_name, func_name)
+
+ func_id = function_info[0]
+
+ data = {
+ "description": "This is a procedure update comment",
+ "id": func_id
+ }
+
+ put_response = self.tester.put(
+ self.url + str(utils.SERVER_GROUP) +
+ '/' + str(self.server_id) + '/' + str(self.db_id) + '/' +
+ str(self.schema_id) + '/' +
+ str(func_id),
+ data=json.dumps(data),
+ follow_redirects=True)
+ self.assertEquals(put_response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_add.py
new file mode 100644
index 0000000..b5cdf17
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_add.py
@@ -0,0 +1,90 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class ProcedureAddTestCase(BaseTestGenerator):
+ """ This class will add new procedure under schema node. """
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching default URL for procedure node.
+ ('Fetch Procedure Node URL', dict(
+ url='/browser/procedure/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will add procedure under schema node. """
+ super(ProcedureAddTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ if self.server_type == "pg" and\
+ self.server_version < 110000:
+ message = "Procedures are not supported by PG < 110000."
+ self.skipTest(message)
+
+ db_user = self.server["username"]
+ data = {
+ "acl": [
+ {
+ "grantee": db_user,
+ "grantor": db_user,
+ "privileges":
+ [
+ {
+ "privilege_type": "X",
+ "privilege": True,
+ "with_grant": True
+ }
+ ]
+ }
+ ],
+ "arguments": [],
+ "funcowner": db_user,
+ "lanname": "sql",
+ "name": "test_pg_11_proc",
+ "options": [],
+ "proleakproof": True,
+ "pronamespace": 2200,
+ "prosecdef": True,
+ "prosrc": "BEGIN RAISE EXCEPTION 'command % is disabled',"
+ " tg_tag; END;",
+ "seclabels": [],
+ "variables": [
+ {
+ "name": "enable_sort",
+ "value": True
+ }
+ ]
+ }
+
+ data["name"] = "test_proc_add_%s" % str(uuid.uuid4())[1:8]
+ if self.server_type == 'pg':
+ data['prosrc'] = 'SELECT 1;'
+ if self.schema_id:
+ data['pronamespace'] = self.schema_id
+ else:
+ self.schema_id = data['pronamespace']
+ response = self.tester.post(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' + str(self.db_id) + '/' +
+ str(self.schema_id) + '/', data=json.dumps(data),
+ content_type='html/json'
+ )
+
+ self.assertEquals(response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_delete.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_delete.py
new file mode 100644
index 0000000..4556b9f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_delete.py
@@ -0,0 +1,56 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class procedureDeleteTestCase(BaseTestGenerator):
+ """ This class will delete the procedure under schema node. """
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching default URL for procedure node.
+ ('Fetch Procedure Node URL',
+ dict(url='/browser/procedure/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will delete procedure under database node. """
+ super(procedureDeleteTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ if self.server_type == "pg" and\
+ self.server_version < 110000:
+ message = "Procedures are not supported by PG < 110000."
+ self.skipTest(message)
+
+ func_name = "test_procedure_delete_%s" % str(uuid.uuid4())[1:8]
+ proc_info = funcs_utils.create_procedure(
+ self.server, self.db_name, self.schema_name, func_name,
+ self.server_type)
+
+ proc_id = proc_info[0]
+ response = self.tester.delete(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' +
+ str(self.db_id) + '/' +
+ str(self.schema_id) + '/' + str(proc_id),
+ content_type='html/json'
+ )
+ self.assertEquals(response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_get.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_get.py
new file mode 100644
index 0000000..2b15878
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_get.py
@@ -0,0 +1,55 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class ProcedureGetTestCase(BaseTestGenerator):
+ """This class will fetch added procedure under schema node."""
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching default URL for procedure node.
+ ('Fetch Procedure Node URL',
+ dict(url='/browser/procedure/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will get procedure under database node. """
+ super(ProcedureGetTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ if self.server_type == "pg" and\
+ self.server_version < 110000:
+ message = "Procedures are not supported by PG < 110000."
+ self.skipTest(message)
+
+ func_name = "test_procedure_get_%s" % str(uuid.uuid4())[1:8]
+ proc_info = funcs_utils.create_procedure(
+ self.server, self.db_name, self.schema_name, func_name,
+ self.server_type)
+
+ proc_id = proc_info[0]
+ response = self.tester.get(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' +
+ str(self.db_id) + '/' +
+ str(self.schema_id) + '/' + str(proc_id),
+ content_type='html/json')
+ self.assertEquals(response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_put.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_put.py
new file mode 100644
index 0000000..73ba1ae
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_put.py
@@ -0,0 +1,62 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2018, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+import uuid
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class ProcedurePutTestCase(BaseTestGenerator):
+ """ This class will update new procedure under schema node. """
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching default URL for procedure node.
+ ('Fetch Procedure Node URL',
+ dict(url='/browser/procedure/obj/'))
+ ]
+
+ def runTest(self):
+ """ This function will update procedure under database node. """
+ super(ProcedurePutTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ if self.server_type == "pg" and\
+ self.server_version < 110000:
+ message = "Procedures are not supported by PG < 110000."
+ self.skipTest(message)
+
+ func_name = "test_procedure_put_%s" % str(uuid.uuid4())[1:8]
+ proc_info = funcs_utils.create_procedure(
+ self.server, self.db_name, self.schema_name, func_name,
+ self.server_type)
+
+ proc_id = proc_info[0]
+ data = {
+ "description": "This is procedure update comment",
+ "id": proc_id
+ }
+
+ put_response = self.tester.put(
+ self.url + str(utils.SERVER_GROUP) +
+ '/' + str(self.server_id) + '/' + str(self.db_id) + '/' +
+ str(self.schema_id) + '/' +
+ str(proc_id),
+ data=json.dumps(data),
+ follow_redirects=True)
+ self.assertEquals(put_response.status_code, 200)
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_add.py
index d238bf0..e99f7c3 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_add.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_add.py
@@ -10,14 +10,11 @@
import json
import uuid
-from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
- utils as schema_utils
from pgadmin.browser.server_groups.servers.databases.tests import utils as \
database_utils
-from pgadmin.utils import server_utils as server_utils
from pgadmin.utils.route import BaseTestGenerator
-from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
class TriggerFuncAddTestCase(BaseTestGenerator):
@@ -32,30 +29,7 @@ class TriggerFuncAddTestCase(BaseTestGenerator):
def runTest(self):
""" This function will add trigger function under schema node. """
super(TriggerFuncAddTestCase, self).runTest()
- db_name = parent_node_dict["database"][-1]["db_name"]
- schema_info = parent_node_dict["schema"][-1]
- server_id = schema_info["server_id"]
- db_id = schema_info["db_id"]
- prorettypename = "event_trigger/trigger"
- server_con = server_utils.connect_server(self, server_id)
- if not server_con["info"] == "Server connected.":
- raise Exception("Could not connect to server to add resource "
- "groups.")
- if "type" in server_con["data"]:
- if server_con["data"]["version"] < 90300:
- prorettypename = "trigger"
-
- db_con = database_utils.connect_database(self, utils.SERVER_GROUP,
- server_id, db_id)
- if not db_con['data']["connected"]:
- raise Exception("Could not connect to database to add a function.")
- schema_id = schema_info["schema_id"]
- schema_name = schema_info["schema_name"]
- schema_response = schema_utils.verify_schemas(self.server,
- db_name,
- schema_name)
- if not schema_response:
- raise Exception("Could not find the schema to add a function.")
+ self = funcs_utils.set_up(self)
db_user = self.server["username"]
data = {
"acl": [
@@ -79,7 +53,7 @@ class TriggerFuncAddTestCase(BaseTestGenerator):
"options": [],
"proleakproof": True,
"pronamespace": 2200,
- "prorettypename": prorettypename,
+ "prorettypename": self.prorettypename,
"prosecdef": True,
"prosrc": "BEGIN RAISE EXCEPTION 'command % is disabled',"
" tg_tag; END;",
@@ -98,16 +72,17 @@ class TriggerFuncAddTestCase(BaseTestGenerator):
for func_type in trigger_func_types:
data['prorettypename'] = func_type
data["name"] = "test_event_add_%s" % str(uuid.uuid4())[1:8]
- if schema_id:
- data['pronamespace'] = schema_id
+ if self.schema_id:
+ data['pronamespace'] = self.schema_id
else:
- schema_id = data['pronamespace']
+ self.schema_id = data['pronamespace']
response = self.tester.post(
self.url + str(utils.SERVER_GROUP) + '/' +
- str(server_id) + '/' + str(db_id) + '/' + str(schema_id) +
+ str(self.server_id) + '/' + str(self.db_id) +
+ '/' + str(self.schema_id) +
'/', data=json.dumps(data), content_type='html/json'
)
self.assertEquals(response.status_code, 200)
# Disconnect the database
- database_utils.disconnect_database(self, server_id, db_id)
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_delete.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_delete.py
index 3d1fe9f..f722470 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_delete.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_delete.py
@@ -9,13 +9,9 @@
import uuid
-from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
- utils as schema_utils
from pgadmin.browser.server_groups.servers.databases.tests import utils as \
database_utils
-from pgadmin.utils import server_utils as server_utils
from pgadmin.utils.route import BaseTestGenerator
-from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
from . import utils as trigger_funcs_utils
@@ -29,49 +25,27 @@ class TriggerFuncDeleteTestCase(BaseTestGenerator):
dict(url='/browser/trigger_function/obj/'))
]
- def setUp(self):
- super(TriggerFuncDeleteTestCase, self).setUp()
- self.db_name = parent_node_dict["database"][-1]["db_name"]
- self.schema_name = parent_node_dict["schema"][-1]["schema_name"]
- self.schema_id = parent_node_dict["schema"][-1]["schema_id"]
-
def runTest(self):
""" This function will delete trigger function under database node. """
- schema_info = parent_node_dict["schema"][-1]
- server_id = schema_info["server_id"]
- db_id = schema_info["db_id"]
+ super(TriggerFuncDeleteTestCase, self).setUp()
+ self = trigger_funcs_utils.set_up(self)
+
func_name = "test_event_delete_%s" % str(uuid.uuid4())[1:8]
- server_con = server_utils.connect_server(self, server_id)
- if not server_con["info"] == "Server connected.":
- raise Exception("Could not connect to server to add resource "
- "groups.")
- server_version = 0
- if "type" in server_con["data"]:
- if server_con["data"]["version"] < 90300:
- server_version = server_con["data"]["version"]
- self.function_info = trigger_funcs_utils.create_trigger_function(
+ function_info = trigger_funcs_utils.create_trigger_function(
self.server, self.db_name, self.schema_name, func_name,
- server_version)
+ self.server_version)
- db_con = database_utils.connect_database(self, utils.SERVER_GROUP,
- server_id, db_id)
- if not db_con['data']["connected"]:
- raise Exception("Could not connect to database to add collation.")
- schema_response = schema_utils.verify_schemas(self.server,
- self.db_name,
- self.schema_name)
- if not schema_response:
- raise Exception("Could not find the schema to add the collation.")
- trigger_func_id = self.function_info[0]
+ trigger_func_id = function_info[0]
response = self.tester.delete(
- self.url + str(utils.SERVER_GROUP) + '/' + str(server_id) + '/' +
- str(db_id) + '/' +
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' +
+ str(self.db_id) + '/' +
str(self.schema_id) + '/' + str(trigger_func_id),
content_type='html/json'
)
self.assertEquals(response.status_code, 200)
# Disconnect the database
- database_utils.disconnect_database(self, server_id, db_id)
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
def tearDown(self):
pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_get.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_get.py
index 7eb0d2a..15ae1ba 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_get.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_get.py
@@ -9,13 +9,9 @@
import uuid
-from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
- utils as schema_utils
from pgadmin.browser.server_groups.servers.databases.tests import utils as \
database_utils
-from pgadmin.utils import server_utils as server_utils
from pgadmin.utils.route import BaseTestGenerator
-from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
from . import utils as trigger_funcs_utils
@@ -29,49 +25,26 @@ class TriggerFuncGetTestCase(BaseTestGenerator):
dict(url='/browser/trigger_function/obj/'))
]
- def setUp(self):
- super(TriggerFuncGetTestCase, self).setUp()
- self.db_name = parent_node_dict["database"][-1]["db_name"]
- self.schema_name = parent_node_dict["schema"][-1]["schema_name"]
- self.schema_id = parent_node_dict["schema"][-1]["schema_id"]
-
def runTest(self):
""" This function will delete trigger function under database node. """
- schema_info = parent_node_dict["schema"][-1]
- server_id = schema_info["server_id"]
- db_id = schema_info["db_id"]
- func_name = "test_event_get_%s" % str(uuid.uuid4())[1:8]
- db_user = self.server["username"]
- server_con = server_utils.connect_server(self, server_id)
- if not server_con["info"] == "Server connected.":
- raise Exception("Could not connect to server to add resource "
- "groups.")
- server_version = 0
- if "type" in server_con["data"]:
- if server_con["data"]["version"] < 90300:
- server_version = server_con["data"]["version"]
- self.function_info = trigger_funcs_utils.create_trigger_function(
+ super(TriggerFuncGetTestCase, self).setUp()
+ self = trigger_funcs_utils.set_up(self)
+
+ func_name = "test_event_delete_%s" % str(uuid.uuid4())[1:8]
+ function_info = trigger_funcs_utils.create_trigger_function(
self.server, self.db_name, self.schema_name, func_name,
- server_version)
+ self.server_version)
- db_con = database_utils.connect_database(self, utils.SERVER_GROUP,
- server_id, db_id)
- if not db_con['data']["connected"]:
- raise Exception("Could not connect to database to add collation.")
- schema_response = schema_utils.verify_schemas(self.server,
- self.db_name,
- self.schema_name)
- if not schema_response:
- raise Exception("Could not find the schema to add the collation.")
- trigger_func_id = self.function_info[0]
+ trigger_func_id = function_info[0]
response = self.tester.get(
- self.url + str(utils.SERVER_GROUP) + '/' + str(server_id) + '/' +
- str(db_id) + '/' +
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(self.server_id) + '/' +
+ str(self.db_id) + '/' +
str(self.schema_id) + '/' + str(trigger_func_id),
content_type='html/json')
self.assertEquals(response.status_code, 200)
# Disconnect the database
- database_utils.disconnect_database(self, server_id, db_id)
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
def tearDown(self):
pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_put.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_put.py
index 63d4c08..78ddc8f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_put.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_trigger_func_put.py
@@ -10,13 +10,9 @@
import json
import uuid
-from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
- utils as schema_utils
from pgadmin.browser.server_groups.servers.databases.tests import utils as \
database_utils
-from pgadmin.utils import server_utils as server_utils
from pgadmin.utils.route import BaseTestGenerator
-from regression import parent_node_dict
from regression.python_test_utils import test_utils as utils
from . import utils as trigger_funcs_utils
@@ -30,63 +26,33 @@ class TriggerFuncPutTestCase(BaseTestGenerator):
dict(url='/browser/trigger_function/obj/'))
]
- def setUp(self):
- super(TriggerFuncPutTestCase, self).setUp()
- self.db_name = parent_node_dict["database"][-1]["db_name"]
- self.schema_name = parent_node_dict["schema"][-1]["schema_name"]
- self.schema_id = parent_node_dict["schema"][-1]["schema_id"]
-
def runTest(self):
""" This function will update trigger function under database node. """
- schema_info = parent_node_dict["schema"][-1]
- server_id = schema_info["server_id"]
- db_id = schema_info["db_id"]
- func_name = "test_event_put_%s" % str(uuid.uuid4())[1:8]
- server_con = server_utils.connect_server(self, server_id)
- if not server_con["info"] == "Server connected.":
- raise Exception("Could not connect to server to add resource "
- "groups.")
- server_version = 0
- if "type" in server_con["data"]:
- if server_con["data"]["version"] < 90300:
- server_version = server_con["data"]["version"]
- self.function_info = trigger_funcs_utils.create_trigger_function(
+ super(TriggerFuncPutTestCase, self).setUp()
+ self = trigger_funcs_utils.set_up(self)
+
+ func_name = "test_event_delete_%s" % str(uuid.uuid4())[1:8]
+ function_info = trigger_funcs_utils.create_trigger_function(
self.server, self.db_name, self.schema_name, func_name,
- server_version)
- db_con = database_utils.connect_database(self, utils.SERVER_GROUP,
- server_id, db_id)
- if not db_con['data']["connected"]:
- raise Exception("Could not connect to database to add collation.")
- schema_response = schema_utils.verify_schemas(self.server,
- self.db_name,
- self.schema_name)
- if not schema_response:
- raise Exception("Could not find the schema to add the collation.")
- func_name = self.function_info[1]
- func_response = trigger_funcs_utils.verify_trigger_function(
- self.server,
- self.db_name,
- func_name)
- if not func_response:
- raise Exception("Could not find the trigger function to update"
- " it's details.")
+ self.server_version)
+
+ trigger_func_id = function_info[0]
- trigger_func_id = self.function_info[0]
data = {
- "description": "This is trigger function update comment",
+ "description": "This is a trigger function update comment",
"id": trigger_func_id
}
put_response = self.tester.put(
self.url + str(utils.SERVER_GROUP) +
- '/' + str(server_id) + '/' + str(db_id) + '/' +
+ '/' + str(self.server_id) + '/' + str(self.db_id) + '/' +
str(self.schema_id) + '/' +
str(trigger_func_id),
data=json.dumps(data),
follow_redirects=True)
self.assertEquals(put_response.status_code, 200)
# Disconnect the database
- database_utils.disconnect_database(self, server_id, db_id)
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
def tearDown(self):
pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py
index 4facf60..3606b45 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py
@@ -13,6 +13,11 @@ import sys
import traceback
from regression.python_test_utils import test_utils as utils
+from pgadmin.utils import server_utils as server_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
+ utils as schema_utils
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
def create_trigger_function(server, db_name, schema_name, func_name,
@@ -92,3 +97,118 @@ def verify_trigger_function(server, db_name, func_name):
functions = pg_cursor.fetchone()
connection.close()
return functions
+
+
+def create_procedure(server, db_name, schema_name, func_name, s_type):
+ """This function add the procedure to schema"""
+ try:
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'],
+ server['sslmode'])
+ pg_cursor = connection.cursor()
+ if s_type == 'pg':
+ query = "CREATE PROCEDURE " + schema_name + "." + func_name + \
+ "()" \
+ " LANGUAGE 'sql'" \
+ " SECURITY DEFINER AS $$" \
+ " SELECT 1; $$;"
+ else:
+ query = "CREATE PROCEDURE " + schema_name + "." + func_name + \
+ "()" \
+ " SECURITY DEFINER AS $BODY$ BEGIN" \
+ " NULL; END; $BODY$"
+ pg_cursor.execute(query)
+ connection.commit()
+ # Get 'oid' from newly created function
+ pg_cursor.execute("SELECT pro.oid, pro.proname FROM"
+ " pg_proc pro WHERE pro.proname='%s'" %
+ func_name)
+ functions = pg_cursor.fetchone()
+ connection.close()
+ return functions
+ except Exception:
+ traceback.print_exc(file=sys.stderr)
+
+
+def create_function(server, db_name, schema_name, func_name):
+ """This function add the procedure to schema"""
+ try:
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'],
+ server['sslmode'])
+ pg_cursor = connection.cursor()
+ query = "CREATE FUNCTION " + schema_name + "." + func_name + \
+ "()" \
+ " RETURNS integer LANGUAGE 'sql' STABLE LEAKPROOF" \
+ " SECURITY DEFINER AS $$" \
+ " SELECT 1; $$;"
+ pg_cursor.execute(query)
+ connection.commit()
+ # Get 'oid' from newly created function
+ pg_cursor.execute("SELECT pro.oid, pro.proname FROM"
+ " pg_proc pro WHERE pro.proname='%s'" %
+ func_name)
+ functions = pg_cursor.fetchone()
+ connection.close()
+ return functions
+ except Exception:
+ traceback.print_exc(file=sys.stderr)
+
+
+def verify_procedure(server, db_name, proc_name):
+ """This function verifies the procedure in db"""
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'],
+ server['sslmode'])
+ pg_cursor = connection.cursor()
+ pg_cursor.execute("SELECT pro.oid, pro.proname FROM"
+ " pg_proc pro WHERE pro.proname='%s'" %
+ proc_name)
+ procs = pg_cursor.fetchone()
+ connection.close()
+ return procs
+
+
+def set_up(obj):
+ """Common set up function"""
+ from regression import parent_node_dict
+
+ obj.db_name = parent_node_dict["database"][-1]["db_name"]
+ schema_info = parent_node_dict["schema"][-1]
+ obj.server_id = schema_info["server_id"]
+ obj.db_id = schema_info["db_id"]
+ obj.prorettypename = "event_trigger/trigger"
+ server_con = server_utils.connect_server(obj, obj.server_id)
+
+ if not server_con["info"] == "Server connected.":
+ raise Exception("Could not connect to server.")
+ if "version" in server_con["data"]:
+ obj.server_version = server_con["data"]["version"]
+ if server_con["data"]["version"] < 90300:
+ obj.prorettypename = "trigger"
+ if "type" in server_con["data"]:
+ obj.server_type = server_con["data"]["type"]
+
+ db_con = database_utils.connect_database(obj, utils.SERVER_GROUP,
+ obj.server_id,
+ obj.db_id)
+ if not db_con['data']["connected"]:
+ raise Exception("Could not connect to database.")
+ obj.schema_id = schema_info["schema_id"]
+ obj.schema_name = schema_info["schema_name"]
+ schema_response = schema_utils.verify_schemas(obj.server,
+ obj.db_name,
+ obj.schema_name)
+ if not schema_response:
+ raise Exception("Could not find the schema.")
+
+ return obj
diff --git a/web/pgadmin/browser/utils.py b/web/pgadmin/browser/utils.py
index 00a72dd..7f6f19c 100644
--- a/web/pgadmin/browser/utils.py
+++ b/web/pgadmin/browser/utils.py
@@ -51,7 +51,9 @@ class PGChildModule(object):
def __init__(self, *args, **kwargs):
self.min_ver = 0
- self.max_ver = 1000000000
+ self.max_ver = 1100000000
+ self.min_ppasver = 0
+ self.max_ppasver = 1100000000
self.server_type = None
self.min_gpdbver = 80323
self.max_gpdbver = 1000000000
@@ -72,6 +74,9 @@ class PGChildModule(object):
if self.server_type is None or manager.server_type in self.server_type:
min_server_version = self.min_ver
max_server_version = self.max_ver
+ if manager.server_type == 'ppas':
+ min_server_version = self.min_ppasver
+ max_server_version = self.max_ppasver
if manager.server_type == 'gpdb':
min_server_version = self.min_gpdbver
max_server_version = self.max_gpdbver
diff --git a/web/pgadmin/utils/versioned_template_loader.py b/web/pgadmin/utils/versioned_template_loader.py
index 3481978..dadaeba 100644
--- a/web/pgadmin/utils/versioned_template_loader.py
+++ b/web/pgadmin/utils/versioned_template_loader.py
@@ -75,7 +75,8 @@ def get_version_mapping(template):
{'name': "default", 'number': 0}
)
- return ({'name': "10_plus", 'number': 100000},
+ return ({'name': "11_plus", 'number': 110000},
+ {'name': "10_plus", 'number': 100000},
{'name': "9.6_plus", 'number': 90600},
{'name': "9.5_plus", 'number': 90500},
{'name': "9.4_plus", 'number': 90400},
