public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: Victoria Henry <[email protected]>
Cc: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin4][Patch]: RM 3362 - Fix the functions for PG v11, and add support procedure for PG v11
Date: Wed, 13 Jun 2018 09:32:48 +0530
Message-ID: <CAFOhELdy8a=pjCFc_LdJ7MMmiqBcj+X8nSVKMvpEygn-e-=aiw@mail.gmail.com> (raw)
In-Reply-To: <CANxYE3L=oWCmtxZanz=N-QFoiR_Q_AyzkXJ_DPCq_9V2WpcU1g@mail.gmail.com>
References: <CAFOhELdzaBaPE1ev9t61+e3fbTDYNsMod1wMLRmG3YAWP5jpug@mail.gmail.com>
<CAFOhELdQLJHccik87AkTPY5nop0P3mF1xy2wEBNkoSSssmumMQ@mail.gmail.com>
<CA+OCxowGMOcrHAv4Q4PSu58PjQV3DfYtRuvba6wg-G_27uMjhA@mail.gmail.com>
<CAFOhELfWXEyeauH_SgVvWBEtKxTqRFyDCUY8Jvr7qnGUfN=F9A@mail.gmail.com>
<CANxYE3L=oWCmtxZanz=N-QFoiR_Q_AyzkXJ_DPCq_9V2WpcU1g@mail.gmail.com>
Hi Victoria,
The updated patch is attached.
On Tue, Jun 12, 2018 at 9:36 PM, Victoria Henry <[email protected]> wrote:
> Hi Khushboo,
>
> The following change is allowing the creation of procedures in postgresql
> versions less then 11 and also GreenPlum
>
> --- 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;
>
> Fixed.
> Now that the Procedures are a thing in Postgresql maybe they should live
> in their own module.
>
The main functionalities of the functions and procedures are almost same
and we have inherited most of the things from function itself.
So, as per me they should live in one module.
> In the tests for trigger functions we are not consistent on the naming of
> the utils , in some places we call it funcs_utils in others
> trigger_funcs_utils.
>
Fixed.
> Thanks
>
> Victoria & Joao
>
> Thanks,
Khushboo
> On Tue, Jun 12, 2018 at 3:10 AM Khushboo Vashi <
> [email protected]> wrote:
>
>> Hi,
>>
>> Please find the attached updated patch.
>>
>> On Fri, Jun 8, 2018 at 2:21 PM, Dave Page <[email protected]> wrote:
>>
>>> Hi
>>>
>>> On Thu, Jun 7, 2018 at 11:27 AM, Khushboo Vashi <
>>> [email protected]> wrote:
>>>
>>>> Hi,
>>>>
>>>> Please ignore my previous patch, find the attached updated one.
>>>>
>>>
>>> I found a couple of issues with this:
>>>
>>> - Clicking the + button on the Parameters tab does nothing in either
>>> Create or Edit modes
>>>
>>> Fixed
>>
>>> - The debugger fails to start (though, perhaps that's because the plugin
>>> doesn't have Ashesh's latest patches in it).
>>>
>>> FYI, I was trying to test the debugger with:
>>>
>>> -- PROCEDURE: public.dummy_proc(integer)
>>>
>>> -- DROP PROCEDURE public.dummy_proc(integer);
>>>
>>> CREATE OR REPLACE PROCEDURE public.dummy_proc(
>>> id integer)
>>> LANGUAGE 'plpgsql'
>>>
>>> AS $BODY$BEGIN
>>> raise notice 'id is %', id;
>>> END;$BODY$;
>>>
>>> Fixed. Tested with the latest code of the plugin.
>>
>>> Thanks!
>>>
>>> Thanks,
>> Khushboo
>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>
>>
Attachments:
[text/x-patch] RM_3362_ver3.patch (104.6K, 3-RM_3362_ver3.patch)
download | inline diff:
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..7b2ba2d 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
@@ -1502,7 +1502,10 @@ class FunctionView(PGChildNodeView, DataTypeReader):
name = name.replace(arg, formatted_arg)
name = name.replace(')', '\n)')
- sql = "EXEC {0}".format(name)
+ if self.manager.server_type == 'pg':
+ sql = "CALL {0}".format(name)
+ else:
+ sql = "EXEC {0}".format(name)
return ajax_response(response=sql)
@@ -1592,10 +1595,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..765d6e2 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,10 +71,13 @@ define('pgadmin.node.procedure', [
if ('catalog' in node_hierarchy)
return false;
- // Procedures supported only in PPAS
+ // Procedures supported only in PPAS and PG >= 11
return (
- 'server' in node_hierarchy &&
- node_hierarchy['server'].server_type == 'ppas'
+ 'server' in node_hierarchy && (
+ node_hierarchy['server'].server_type == 'ppas' ||
+ (node_hierarchy['server'].server_type == 'pg' &&
+ node_hierarchy['server'].version >= 110000)
+ )
);
},
model: Function.model.extend({
@@ -103,20 +106,24 @@ define('pgadmin.node.procedure', [
return true;
}
switch(this.name){
- case 'provolatility':
+ case 'provolatile':
case 'proisstrict':
- case 'prosecdef':
case 'procost':
case 'proleakproof':
+ return (this.node_info.server.version < 90500 || this.node_info.server.server_type != 'ppas');
case 'variables':
+ 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.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/function/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/create.sql
index 213652c..16c088f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/create.sql
@@ -13,7 +13,7 @@ CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ con
{% endif -%}
)
{% endif -%}
- RETURNS{% if data.proretset (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 %}
+ 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 %}
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..3f9151f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/create.sql
@@ -0,0 +1,50 @@
+{% 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 %}
+{% 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.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..e1629e9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/pg/sql/default/update.sql
@@ -0,0 +1,106 @@
+{% 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 %}
+{% 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 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 -%}
+{% 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, '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..e2faa4c 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,15 +9,11 @@
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
+from . import utils as funcs_utils
class TriggerFuncDeleteTestCase(BaseTestGenerator):
@@ -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 = 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 = 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..53fdbeb 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,15 +9,11 @@
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
+from . import utils as funcs_utils
class TriggerFuncGetTestCase(BaseTestGenerator):
@@ -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 = funcs_utils.set_up(self)
+
+ func_name = "test_event_delete_%s" % str(uuid.uuid4())[1:8]
+ function_info = 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..f1136d5 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,15 +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 trigger_funcs_utils
+from . import utils as funcs_utils
class TriggerFuncPutTestCase(BaseTestGenerator):
@@ -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 = funcs_utils.set_up(self)
+
+ func_name = "test_event_delete_%s" % str(uuid.uuid4())[1:8]
+ function_info = 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/tools/debugger/__init__.py b/web/pgadmin/tools/debugger/__init__.py
index c0e89f5..1bf797c 100644
--- a/web/pgadmin/tools/debugger/__init__.py
+++ b/web/pgadmin/tools/debugger/__init__.py
@@ -365,8 +365,11 @@ def init_function(node_type, sid, did, scid, fid, trid=None):
# Check server type is ppas or not
ppas_server = False
+ is_proc_supported = False
if server_type == 'ppas':
ppas_server = True
+ else:
+ is_proc_supported = True if manager.version >= 110000 else False
# Set the template path required to read the sql files
template_path = 'debugger/sql'
@@ -395,7 +398,10 @@ def init_function(node_type, sid, did, scid, fid, trid=None):
sql = render_template(
"/".join([template_path, 'get_function_debug_info.sql']),
is_ppas_database=ppas_server,
- hasFeatureFunctionDefaults=True, fid=fid
+ hasFeatureFunctionDefaults=True,
+ fid=fid,
+ is_proc_supported=is_proc_supported
+
)
status, r_set = conn.execute_dict(sql)
if not status:
@@ -509,6 +515,7 @@ def init_function(node_type, sid, did, scid, fid, trid=None):
'oid': fid,
'name': r_set['rows'][0]['name'],
'is_func': r_set['rows'][0]['isfunc'],
+ 'is_ppas_database': ppas_server,
'is_callable': False,
'schema': r_set['rows'][0]['schemaname'],
'language': r_set['rows'][0]['lanname'],
@@ -765,6 +772,7 @@ def initialize_target(debug_type, sid, did, scid, func_id, tri_id=None):
'oid': func_data['oid'],
'name': func_data['name'],
'is_func': func_data['is_func'],
+ 'is_ppas_database': func_data['is_ppas_database'],
'is_callable': func_data['is_callable'],
'schema': func_data['schema'],
'language': func_data['language'],
@@ -1062,7 +1070,8 @@ def start_debugger_listener(trans_id):
func_name=func_name,
is_func=session_function_data['is_func'],
ret_type=session_function_data['return_type'],
- data=session_function_data['args_value']
+ data=session_function_data['args_value'],
+ is_ppas_database=session_function_data['is_ppas_database']
)
status, result = conn.execute_async(str_query)
@@ -1946,8 +1955,9 @@ def poll_end_execution_result(trans_id):
status, result = conn.poll()
session_function_data = session['functionData'][str(trans_id)]
if status == ASYNC_OK and \
- not session_function_data['is_func'] and \
- session_function_data['language'] == 'edbspl':
+ not session_function_data['is_func'] and\
+ (session_function_data['language'] == 'edbspl' or
+ session_function_data['language'] == 'plpgsql'):
status = 'Success'
additional_msgs = conn.messages()
if len(additional_msgs) > 0:
diff --git a/web/pgadmin/tools/debugger/templates/debugger/sql/execute_plpgsql.sql b/web/pgadmin/tools/debugger/templates/debugger/sql/execute_plpgsql.sql
index b82578b..134a17f 100644
--- a/web/pgadmin/tools/debugger/templates/debugger/sql/execute_plpgsql.sql
+++ b/web/pgadmin/tools/debugger/templates/debugger/sql/execute_plpgsql.sql
@@ -1,6 +1,8 @@
{### Create executer function for plpgsql function debugging ###}
-{% if not is_func %}
+{% if is_ppas_database and not is_func %}
EXEC {{ func_name }} (
+{% elif not is_func %}
+ CALL {{ func_name }} (
{% elif ret_type == 'record' %}
SELECT {{ func_name }} (
{% else %}
diff --git a/web/pgadmin/tools/debugger/templates/debugger/sql/get_function_debug_info.sql b/web/pgadmin/tools/debugger/templates/debugger/sql/get_function_debug_info.sql
index 701f5ee..2807cad 100644
--- a/web/pgadmin/tools/debugger/templates/debugger/sql/get_function_debug_info.sql
+++ b/web/pgadmin/tools/debugger/templates/debugger/sql/get_function_debug_info.sql
@@ -41,7 +41,11 @@ SELECT
0 AS pkgconsoid,
n.oid AS schema,
n.nspname AS schemaname,
- true AS isfunc,
+ {% if is_proc_supported %}
+ CASE WHEN p.prokind in ('f', 'w') THEN TRUE ELSE FALSE END AS isfunc,
+ {% else %}
+ TRUE AS isfunc,
+ {% endif %}
{%endif%}
pg_catalog.pg_get_function_identity_arguments(p.oid) AS signature,
@@ -61,4 +65,4 @@ FROM
{% endif %}
{% if fid %}
WHERE p.oid = {{fid}}::oid;
-{% endif %}
\ No newline at end of file
+{% endif %}
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},
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [pgAdmin4][Patch]: RM 3362 - Fix the functions for PG v11, and add support procedure for PG v11
In-Reply-To: <CAFOhELdy8a=pjCFc_LdJ7MMmiqBcj+X8nSVKMvpEygn-e-=aiw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox