Hi,
Please find the attached patch to fix RM 2089 : PARALLEL SAFE support for
functions missing.
Fix: Incorporated the PARALLEL SAFE support for the functions and
procedures for PostgreSQL 9.6.
Thanks,
Khushboo
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 551f8bb..e80275b 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
@@ -1075,6 +1075,7 @@ class FunctionView(PGChildNodeView, DataTypeReader):
"""
vol_dict = {'v': 'VOLATILE', 's': 'STABLE', 'i': 'IMMUTABLE'}
+ parallel_dict = {'u': 'UNSAFE', 's': 'SAFE', 'r': 'RESTRICTED'}
# Get Schema Name from its OID.
if 'pronamespace' in data:
@@ -1083,6 +1084,9 @@ class FunctionView(PGChildNodeView, DataTypeReader):
if 'provolatile' in data:
data['provolatile'] = vol_dict[data['provolatile']]
+ if 'proparallel' in data:
+ data['proparallel'] = parallel_dict[data['proparallel']]
+
if fnid is not None:
# Edit Mode
@@ -1096,11 +1100,15 @@ class FunctionView(PGChildNodeView, DataTypeReader):
if 'provolatile' in old_data:
old_data['provolatile'] = vol_dict[old_data['provolatile']]
+ if 'proparallel' in old_data:
+ old_data['proparallel'] = parallel_dict[old_data['proparallel']]
+
# If any of the below argument is changed,
# then CREATE OR REPLACE SQL statement should be called
fun_change_args = ['lanname', 'prosrc', 'probin', 'prosrc_c',
'provolatile', 'proisstrict', 'prosecdef',
- 'procost', 'proleakproof', 'arguments']
+ 'proparallel', 'procost', 'proleakproof',
+ 'arguments']
data['change_func'] = False
for arg in fun_change_args:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/js/functions.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/js/functions.js
index 86faf8e..dac4294 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/js/functions.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/js/functions.js
@@ -157,6 +157,7 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
proisstrict: undefined,
prosecdef: undefined, /* Security of definer */
proiswindow: undefined, /* Window Function ? */
+ proparallel: undefined, /* Parallel mode */
procost: undefined, /* Estimated execution Cost */
prorows: undefined, /* Estimated number of rows */
proleakproof: undefined,
@@ -277,6 +278,14 @@ function($, _, S, pgAdmin, pgBrowser, alertify) {
group: '{{ _('Options') }}', cell:'boolean', type: 'switch',
disabled: 'isDisabled', visible: 'isVisible'
},{
+ id: 'proparallel', label: '{{ _('Parallel') }}', cell: 'string',
+ control: 'node-ajax-options', type: 'text', group: '{{ _('Options') }}',
+ options:[
+ {'label': '{{ _('UNSAFE') }}', 'value': 'u'},
+ {'label': '{{ _('RESTRICTED') }}', 'value': 'r'},
+ {'label': '{{ _('SAFE') }}', 'value': 's'},
+ ], disabled: 'isDisabled', min_version: 90600
+ },{
id: 'procost', label: '{{ _('Estimated cost') }}', group: '{{ _('Options') }}',
cell:'string', type: 'text', disabled: 'isDisabled'
},{
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_plus/create.sql
new file mode 100644
index 0000000..64bc6b2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_plus/create.sql
@@ -0,0 +1,65 @@
+{% 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.args %}
+{% for p in data.args %}{% 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 ') %} {{ 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 {% else %}NOT LEAKPROOF {% endif %}
+{% if data.proisstrict %}STRICT {% endif %}
+{% if data.prosecdef %}SECURITY DEFINER {% endif %}
+{% if data.proiswindow %}WINDOW {% endif %}
+
+ {% if data.proparallel %}PARALLEL {% if data.proparallel == 'r' %}RESTRICTED{% elif data.proparallel == 's' %}SAFE{% else %}UNSAFE{% endif %}{% endif %}
+{% if data.prorows %}
+
+ 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 %}
+$function$
+{{ data.prosrc }}
+$function${% 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.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/9.6_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_plus/properties.sql
new file mode 100644
index 0000000..b5f5c5d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_plus/properties.sql
@@ -0,0 +1,32 @@
+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
+ proisagg = FALSE
+{% 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/9.6_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_plus/update.sql
new file mode 100644
index 0000000..619f44f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.6_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 o_data.prorows %}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 %}
+$function${{ data.prosrc }}$function${% elif o_data.lanname == 'c' %}
+{{ o_data.probin|qtLiteral }}, {{ o_data.prosrc_c|qtLiteral }}{% else %}
+$function${{ o_data.prosrc }}$function${% 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.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.6_plus/create.sql
new file mode 100644
index 0000000..ea34b10
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.6_plus/create.sql
@@ -0,0 +1,64 @@
+{% 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.args %}
+{% for p in data.args %}{% 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 ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %}
+
+ LANGUAGE {{ data.lanname|qtLiteral }}
+ {% 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.proparallel %}PARALLEL {% if data.proparallel == 'r' %}RESTRICTED{% elif data.proparallel == 's' %}SAFE{% else %}UNSAFE{% endif %}{% endif %}
+{% if data.procost %}
+
+ COST {{data.procost}}{% endif %}{% if data.prorows %}
+
+ 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 %}
+$function$
+{{ data.prosrc }}
+$function${% 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.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/ppas/sql/9.6_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.6_plus/properties.sql
new file mode 100644
index 0000000..ef6ae02
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.6_plus/properties.sql
@@ -0,0 +1,32 @@
+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
+ proisagg = FALSE
+ 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/function/ppas/sql/9.6_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.6_plus/update.sql
new file mode 100644
index 0000000..49eb94e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.6_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 o_data.prorows %}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 %}
+$function${{ data.prosrc }}$function${% elif o_data.lanname == 'c' %}
+{{ o_data.probin|qtLiteral }}, {{ o_data.prosrc_c|qtLiteral }}{% else %}
+$function${{ o_data.prosrc }}$function${% endif -%};
+{% endif -%}
+{% if data.funcowner %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ OWNER TO {{ conn|qtIdent(data.funcowner) }};
+{% endif -%}
+{# The SQL generated below will change priviledges #}
+{% if data.acl %}
+{% if 'deleted' in data.acl %}
+{% for priv in data.acl.deleted %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in data.datacl %}
+{% for priv in data.acl.changed %}
+
+{{ PRIVILEGE.UNSETALL(conn, 'FUNCTION', priv.grantee, name, o_data.pronamespace, o_data.proargtypenames) }}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in data.acl %}
+{% for priv in data.acl.added %}
+
+{{ PRIVILEGE.SET(conn, 'FUNCTION', priv.grantee, name, priv.without_grant, priv.with_grant, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}{% endif -%}
+{% endif -%}
+{% if data.change_func == False %}
+{% if data.variables %}
+{% if 'deleted' in data.variables and data.variables.deleted|length > 0 %}
+
+{{ VARIABLE.UNSET(conn, 'FUNCTION', name, data.variables.deleted, o_data.pronamespace, o_data.proargtypenames) }}
+{% endif -%}
+{% if 'merged_variables' in data and data.merged_variables|length > 0 %}
+
+{{ VARIABLE.SET(conn, 'FUNCTION', name, data.merged_variables, o_data.pronamespace, o_data.proargtypenames) }}
+{% endif -%}
+{% endif -%}
+{% endif -%}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+
+{{ SECLABEL.UNSET(conn, 'FUNCTION', name, r.provider, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+
+{{ SECLABEL.SET(conn, 'FUNCTION', name, r.provider, r.label, o_data.pronamespace, o_data.proargtypenames) }}
+{% endfor %}
+{% endif -%}
+{% if data.description is defined and data.description != o_data.description%}
+
+COMMENT ON FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ IS {{ data.description|qtLiteral }};
+{% endif -%}
+
+{% if data.pronamespace %}
+
+ALTER FUNCTION {{ conn|qtIdent(o_data.pronamespace, name) }}({{o_data.proargtypenames }})
+ SET SCHEMA {{ conn|qtIdent(data.pronamespace) }};
+{% endif -%}
+
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/create.sql
new file mode 100644
index 0000000..62d778e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/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.args is defined %}
+({% for p in data.args %}{% 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 %}
+{% if query_type != 'create' %}
+
+ {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% elif not data.proleakproof %} NOT LEAKPROOF {% endif %}
+{% if data.proisstrict %}STRICT {% endif %}
+{% if data.prosecdef %}SECURITY DEFINER {% endif %}
+{% if data.proparallel %}PARALLEL {% if data.proparallel == 'r' %}RESTRICTED{% elif data.proparallel == 's' %}SAFE{% else %}UNSAFE{% endif %}{% endif %}{% if data.procost %}
+
+ COST {{data.procost}}{% endif %}{% if data.prorows %}
+
+ ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %}
+
+ SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%}
+{% endif %}{% endif %}
+
+AS
+{{ data.prosrc }};
+{% 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.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/ppas/sql/9.6_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/update.sql
new file mode 100644
index 0000000..ed24678
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/update.sql
@@ -0,0 +1,104 @@
+{% 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 PROCEDURE {{ conn|qtIdent(o_data.pronamespace, o_data.name) }}
+ 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 ('proleakproof' in data and data.proleakproof) or ('proleakproof' not in data and o_data.proleakproof) %}LEAKPROOF{% else %}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 '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 o_data.prorows %}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 data.prosrc %}{{ data.prosrc }}{% else %}{{ o_data.prosrc }}{% 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, 'PROCEDURE', 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, 'PROCEDURE', 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 %}
--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
