public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node 6+ messages / 3 participants [nested] [flat]
* [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node @ 2016-06-02 13:55 Surinder Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Surinder Kumar @ 2016-06-02 13:55 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find attached patch for RM#1293. Add support to display GRANT privilege query in SQL Pane. Please review. Thanks, Surinder Kumar -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] display_GRANT_queries_in_SQL_PANE.patch (12.3K, 3-display_GRANT_queries_in_SQL_PANE.patch) download | inline diff: 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 99cf94a..5119b80 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 @@ -886,6 +886,20 @@ class FunctionView(PGChildNodeView, DataTypeReader): scid: Schema Id fnid: Function Id """ + data_prop = self._fetch_properties(gid, sid, did, scid, fnid) + + # Get Schema Name from its OID. + if 'pronamespace' in data_prop: + data_prop['pronamespace'] = self._get_schema(data_prop[ + 'pronamespace']) + + # Parse privilege data + if 'acl' in data_prop: + data_prop['acl'] = parse_priv_to_db(data_prop['acl'], ['X']) + + # Fetch the function definition. + grant_SQL = render_template("/".join([self.sql_template_path, + 'grant.sql']), data=data_prop) if self.node_type == 'procedure': object_type = 'procedure' @@ -902,7 +916,8 @@ class FunctionView(PGChildNodeView, DataTypeReader): object_type = 'function' # Fetch the function definition. SQL = render_template("/".join([self.sql_template_path, - 'get_definition.sql']), fnid=fnid, scid=scid) + 'get_definition.sql']), + fnid=fnid, scid=scid) status, res = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=res) @@ -915,7 +930,7 @@ class FunctionView(PGChildNodeView, DataTypeReader): """.format(object_type.upper(), name) - SQL = sql_header + func_def + SQL = sql_header + func_def + '\n' + grant_SQL SQL = re.sub('\n{2,}', '\n\n', SQL) return ajax_response(response=SQL) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/grant.sql new file mode 100644 index 0000000..eee5257 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% if data.acl %}{% for p in data.acl %}{{ PRIVILEGE.SET(conn, "PROCEDURE", p.grantee, data.name, p.without_grant, p.with_grant, data.pronamespace, data.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/grant.sql new file mode 100644 index 0000000..eee5257 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% if data.acl %}{% for p in data.acl %}{{ PRIVILEGE.SET(conn, "PROCEDURE", p.grantee, data.name, p.without_grant, p.with_grant, data.pronamespace, data.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/grant.sql new file mode 100644 index 0000000..eee5257 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% if data.acl %}{% for p in data.acl %}{{ PRIVILEGE.SET(conn, "PROCEDURE", p.grantee, data.name, p.without_grant, p.with_grant, data.pronamespace, data.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/grant.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/grant.sql new file mode 100644 index 0000000..8523219 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/grant.sql @@ -0,0 +1,2 @@ +{% import 'macros/functions/privilege.macros' as PRIVILEGE %} +{% 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.proargs)}}{% endfor %}{% endif %} ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node @ 2016-06-02 14:02 Surinder Kumar <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Surinder Kumar @ 2016-06-02 14:02 UTC (permalink / raw) To: pgadmin-hackers Please ignore this patch. I will send another patch. On Thu, Jun 2, 2016 at 7:25 PM, Surinder Kumar < [email protected]> wrote: > Hi, > > Please find attached patch for RM#1293. > Add support to display GRANT privilege query in SQL Pane. > > Please review. > > Thanks, > Surinder Kumar > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node @ 2016-06-03 10:01 Surinder Kumar <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Surinder Kumar @ 2016-06-03 10:01 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find attached patch for: 1. RM#1293 - SQL pane is not displaying GRANT queries in functions node 2. RM#1294 - Comments are not visible in sql pane for trigger functions and functions Explanation: Previously we we using 'get_defintion.sql' template to generate SQL for SQL pane for functions, procedure & trigger functions node. but GRANT statements and COMMENTS changes were missing. In order to fix that, now we have used both 'create.sql' and 'get_definition.sql' templates to generate full SQL query for SQL pane. Please review. On Thu, Jun 2, 2016 at 7:32 PM, Surinder Kumar < [email protected]> wrote: > Please ignore this patch. I will send another patch. > > On Thu, Jun 2, 2016 at 7:25 PM, Surinder Kumar < > [email protected]> wrote: > >> Hi, >> >> Please find attached patch for RM#1293. >> Add support to display GRANT privilege query in SQL Pane. >> >> Please review. >> >> Thanks, >> Surinder Kumar >> > > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] fix_for_RM#1293_and_RM#1294.patch (33.7K, 3-fix_for_RM%231293_and_RM%231294.patch) download | inline diff: 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 99cf94a..1e0ebdc 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 @@ -886,28 +886,71 @@ class FunctionView(PGChildNodeView, DataTypeReader): scid: Schema Id fnid: Function Id """ + resp_data = self._fetch_properties(gid, sid, did, scid, fnid) + # Fetch the function definition. + args = '' + cnt = 1 + if 'arguments' in resp_data: + for a in resp_data['arguments']: + if (('argmode' in a and a['argmode'] != 'OUT' and + a['argmode'] is not None + ) or 'argnode' not in a): + if 'argmode' in a: + args += a['argmode'] + " " + if 'argname' in a and a['argname'] != ''\ + and a['argname'] is not None: + args += self.qtIdent( + self.conn, a['argname']) + " " + if 'argtype' in a: + args += a['argtype'] + if cnt < len(resp_data['arguments']): + args += ', ' + cnt += 1 + + resp_data['func_args'] = args.strip(' ') if self.node_type == 'procedure': object_type = 'procedure' - resp_data = self._fetch_properties(gid, sid, did, scid, fnid) # Get SQL to create Function - status, func_def = self._get_sql(gid, sid, did, scid, resp_data, + status, func_defintion = self._get_sql(gid, sid, did, scid, resp_data, None, True) if not status: - return internal_server_error(errormsg=func_def) + return internal_server_error(errormsg=func_defintion) name = resp_data['pronamespace'] + "." + resp_data['name_with_args'] + + # Create mode + func_def = render_template("/".join([self.sql_template_path, + 'create.sql']), + data=resp_data, query_type="create") else: object_type = 'function' - # Fetch the function definition. - SQL = render_template("/".join([self.sql_template_path, - 'get_definition.sql']), fnid=fnid, scid=scid) - status, res = self.conn.execute_2darray(SQL) + + # Get Schema Name from its OID. + if 'pronamespace' in resp_data: + resp_data['pronamespace'] = self._get_schema(resp_data[ + 'pronamespace']) + + # Parse privilege data + if 'acl' in resp_data: + resp_data['acl'] = parse_priv_to_db(resp_data['acl'], ['X']) + + # Create mode + SQL_def = render_template("/".join([self.sql_template_path, + 'get_definition.sql'] + ), data=resp_data, + fnid=fnid, scid=scid) + + status, res = self.conn.execute_2darray(SQL_def) if not status: return internal_server_error(errormsg=res) - func_def, name = res['rows'][0] + func_definition, name = res['rows'][0] + # Create mode + func_def = render_template("/".join([self.sql_template_path, + 'create.sql']), + data=resp_data, query_type="create") sql_header = """-- {0}: {1} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql index b98fc6d..e1da643 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql @@ -3,22 +3,35 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql index 55602bd..42885ea 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql @@ -1,27 +1,40 @@ -{% import 'macros/functions/security.macros' as SECLABLE %} +{% 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.args %} -{% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql index bac6b7e..a0e187c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql @@ -3,26 +3,38 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql index b98fc6d..e1da643 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql @@ -3,22 +3,35 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} 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 737de0d..f0cdbde 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 @@ -3,25 +3,38 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql index bac6b7e..dda7ad7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql @@ -3,26 +3,38 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql index 5b9640e..0a8cb20 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql @@ -3,11 +3,12 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} -{% if not loop.last %},{% endif %} -{% endfor %}) -{% endif %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +{% if not loop.last %}, {% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql index 042b786..96c9a36 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql @@ -3,10 +3,12 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} -{% endfor -%}){% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql index 17e160b..7447d1e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql @@ -3,10 +3,13 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} -{% endfor -%}){% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} +{% if query_type != 'create' %} {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% elif not data.proleakproof %} NOT LEAKPROOF {% endif %} {% if data.proisstrict %}STRICT {% endif %} @@ -17,7 +20,7 @@ CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% i ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %} SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%} -{% endif %} +{% endif %}{% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql index 8e51a49..6e9fd78 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql @@ -6,16 +6,19 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql index ddf6e25..81dcb1a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql @@ -6,19 +6,22 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql index fa36ad8..5445855 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql @@ -3,22 +3,25 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() +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 query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql index 8e51a49..6e9fd78 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql @@ -6,16 +6,19 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql index f7d497e..210cf3a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql @@ -6,19 +6,22 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql index fa36ad8..5b34fbb 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql @@ -6,23 +6,26 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ -{{ data.prosrc }} +{{ data.prosrc }}; $BODY${% endif -%}; {% if data.funcowner %} ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node @ 2016-06-06 13:47 Surinder Kumar <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Surinder Kumar @ 2016-06-06 13:47 UTC (permalink / raw) To: pgadmin-hackers Hi, Please find updated patch: *Issue*: Code changes are not consistent with the present code in functions/__init__.py, like don't rename variable func_def to func_defintion until it is required. Review comment given by Ashesh personally. Please review. On Fri, Jun 3, 2016 at 3:31 PM, Surinder Kumar < [email protected]> wrote: > Hi, > > Please find attached patch for: > 1. RM#1293 - SQL pane is not displaying GRANT queries in functions node > 2. RM#1294 - Comments are not visible in sql pane for trigger functions > and functions > > Explanation: > Previously we we using 'get_defintion.sql' template to generate SQL for > SQL pane for functions, procedure & trigger functions node. > but GRANT statements and COMMENTS changes were missing. In order to fix > that, now we have used both 'create.sql' and 'get_definition.sql' templates > to generate full SQL query > for SQL pane. > > Please review. > > On Thu, Jun 2, 2016 at 7:32 PM, Surinder Kumar < > [email protected]> wrote: > >> Please ignore this patch. I will send another patch. >> >> On Thu, Jun 2, 2016 at 7:25 PM, Surinder Kumar < >> [email protected]> wrote: >> >>> Hi, >>> >>> Please find attached patch for RM#1293. >>> Add support to display GRANT privilege query in SQL Pane. >>> >>> Please review. >>> >>> Thanks, >>> Surinder Kumar >>> >> >> > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [application/octet-stream] fix_for_RM#1293_and_RM#1294_v1.patch (33.3K, 3-fix_for_RM%231293_and_RM%231294_v1.patch) download | inline diff: 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 99cf94a..824e60d 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 @@ -886,10 +886,31 @@ class FunctionView(PGChildNodeView, DataTypeReader): scid: Schema Id fnid: Function Id """ + resp_data = self._fetch_properties(gid, sid, did, scid, fnid) + # Fetch the function definition. + args = '' + cnt = 1 + if 'arguments' in resp_data: + for a in resp_data['arguments']: + if (('argmode' in a and a['argmode'] != 'OUT' and + a['argmode'] is not None + ) or 'argnode' not in a): + if 'argmode' in a: + args += a['argmode'] + " " + if 'argname' in a and a['argname'] != ''\ + and a['argname'] is not None: + args += self.qtIdent( + self.conn, a['argname']) + " " + if 'argtype' in a: + args += a['argtype'] + if cnt < len(resp_data['arguments']): + args += ', ' + cnt += 1 + + resp_data['func_args'] = args.strip(' ') if self.node_type == 'procedure': object_type = 'procedure' - resp_data = self._fetch_properties(gid, sid, did, scid, fnid) # Get SQL to create Function status, func_def = self._get_sql(gid, sid, did, scid, resp_data, @@ -898,16 +919,38 @@ class FunctionView(PGChildNodeView, DataTypeReader): return internal_server_error(errormsg=func_def) name = resp_data['pronamespace'] + "." + resp_data['name_with_args'] + + # Create mode + func_def = render_template("/".join([self.sql_template_path, + 'create.sql']), + data=resp_data, query_type="create") else: object_type = 'function' - # Fetch the function definition. + + # Get Schema Name from its OID. + if 'pronamespace' in resp_data: + resp_data['pronamespace'] = self._get_schema(resp_data[ + 'pronamespace']) + + # Parse privilege data + if 'acl' in resp_data: + resp_data['acl'] = parse_priv_to_db(resp_data['acl'], ['X']) + + # Create mode SQL = render_template("/".join([self.sql_template_path, - 'get_definition.sql']), fnid=fnid, scid=scid) + 'get_definition.sql'] + ), data=resp_data, + fnid=fnid, scid=scid) + status, res = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=res) func_def, name = res['rows'][0] + # Create mode + func_def = render_template("/".join([self.sql_template_path, + 'create.sql']), + data=resp_data, query_type="create") sql_header = """-- {0}: {1} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql index b98fc6d..e1da643 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql @@ -3,22 +3,35 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql index 55602bd..42885ea 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql @@ -1,27 +1,40 @@ -{% import 'macros/functions/security.macros' as SECLABLE %} +{% 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.args %} -{% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql index bac6b7e..a0e187c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql @@ -3,26 +3,38 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql index b98fc6d..e1da643 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql @@ -3,22 +3,35 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} 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 737de0d..f0cdbde 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 @@ -3,25 +3,38 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql index bac6b7e..dda7ad7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql @@ -3,26 +3,38 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql index 5b9640e..0a8cb20 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql @@ -3,11 +3,12 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} -{% if not loop.last %},{% endif %} -{% endfor %}) -{% endif %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +{% if not loop.last %}, {% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql index 042b786..96c9a36 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql @@ -3,10 +3,12 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ 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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} -{% endfor -%}){% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql index 17e160b..7447d1e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql @@ -3,10 +3,13 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} -{% endfor -%}){% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} +{% if query_type != 'create' %} {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% elif not data.proleakproof %} NOT LEAKPROOF {% endif %} {% if data.proisstrict %}STRICT {% endif %} @@ -17,7 +20,7 @@ CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% i ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %} SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%} -{% endif %} +{% endif %}{% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql index 8e51a49..6e9fd78 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql @@ -6,16 +6,19 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql index ddf6e25..81dcb1a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql @@ -6,19 +6,22 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql index fa36ad8..5445855 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql @@ -3,22 +3,25 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() +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 query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql index 8e51a49..6e9fd78 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql @@ -6,16 +6,19 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql index f7d497e..210cf3a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql @@ -6,19 +6,22 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql index fa36ad8..5b34fbb 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql @@ -6,23 +6,26 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ -{{ data.prosrc }} +{{ data.prosrc }}; $BODY${% endif -%}; {% if data.funcowner %} ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re: [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node @ 2016-07-22 07:56 Harshal Dhumal <[email protected]> parent: Surinder Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Harshal Dhumal @ 2016-07-22 07:56 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Hi, PFA rebased patch for RM1293 and RM1294. -- *Harshal Dhumal* *Software Engineer* EnterpriseDB India: http://www.enterprisedb.com The Enterprise PostgreSQL Company On Mon, Jun 6, 2016 at 7:17 PM, Surinder Kumar < [email protected]> wrote: > Hi, > > Please find updated patch: > *Issue*: Code changes are not consistent with the present code in > functions/__init__.py, like don't rename variable func_def to > func_defintion until it is required. > Review comment given by Ashesh personally. > > Please review. > > On Fri, Jun 3, 2016 at 3:31 PM, Surinder Kumar < > [email protected]> wrote: > >> Hi, >> >> Please find attached patch for: >> 1. RM#1293 - SQL pane is not displaying GRANT queries in functions node >> 2. RM#1294 - Comments are not visible in sql pane for trigger functions >> and functions >> >> Explanation: >> Previously we we using 'get_defintion.sql' template to generate SQL for >> SQL pane for functions, procedure & trigger functions node. >> but GRANT statements and COMMENTS changes were missing. In order to fix >> that, now we have used both 'create.sql' and 'get_definition.sql' templates >> to generate full SQL query >> for SQL pane. >> >> Please review. >> >> On Thu, Jun 2, 2016 at 7:32 PM, Surinder Kumar < >> [email protected]> wrote: >> >>> Please ignore this patch. I will send another patch. >>> >>> On Thu, Jun 2, 2016 at 7:25 PM, Surinder Kumar < >>> [email protected]> wrote: >>> >>>> Hi, >>>> >>>> Please find attached patch for RM#1293. >>>> Add support to display GRANT privilege query in SQL Pane. >>>> >>>> Please review. >>>> >>>> Thanks, >>>> Surinder Kumar >>>> >>> >>> >> > > > -- > Sent via pgadmin-hackers mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers > > -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers Attachments: [text/x-patch] fix_for_RM#1293_and_RM#1294_v2.patch (32.2K, 3-fix_for_RM%231293_and_RM%231294_v2.patch) download | inline diff: 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 7d5d129..75ba022 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 @@ -889,10 +889,31 @@ class FunctionView(PGChildNodeView, DataTypeReader): scid: Schema Id fnid: Function Id """ + resp_data = self._fetch_properties(gid, sid, did, scid, fnid) + # Fetch the function definition. + args = '' + cnt = 1 + if 'arguments' in resp_data: + for a in resp_data['arguments']: + if (('argmode' in a and a['argmode'] != 'OUT' and + a['argmode'] is not None + ) or 'argnode' not in a): + if 'argmode' in a: + args += a['argmode'] + " " + if 'argname' in a and a['argname'] != ''\ + and a['argname'] is not None: + args += self.qtIdent( + self.conn, a['argname']) + " " + if 'argtype' in a: + args += a['argtype'] + if cnt < len(resp_data['arguments']): + args += ', ' + cnt += 1 + + resp_data['func_args'] = args.strip(' ') if self.node_type == 'procedure': object_type = 'procedure' - resp_data = self._fetch_properties(gid, sid, did, scid, fnid) # Get SQL to create Function status, func_def = self._get_sql(gid, sid, did, scid, resp_data, @@ -901,16 +922,38 @@ class FunctionView(PGChildNodeView, DataTypeReader): return internal_server_error(errormsg=func_def) name = resp_data['pronamespace'] + "." + resp_data['name_with_args'] + + # Create mode + func_def = render_template("/".join([self.sql_template_path, + 'create.sql']), + data=resp_data, query_type="create") else: object_type = 'function' - # Fetch the function definition. + + # Get Schema Name from its OID. + if 'pronamespace' in resp_data: + resp_data['pronamespace'] = self._get_schema(resp_data[ + 'pronamespace']) + + # Parse privilege data + if 'acl' in resp_data: + resp_data['acl'] = parse_priv_to_db(resp_data['acl'], ['X']) + + # Create mode SQL = render_template("/".join([self.sql_template_path, - 'get_definition.sql']), fnid=fnid, scid=scid) + 'get_definition.sql'] + ), data=resp_data, + fnid=fnid, scid=scid) + status, res = self.conn.execute_2darray(SQL) if not status: return internal_server_error(errormsg=res) func_def, name = res['rows'][0] + # Create mode + func_def = render_template("/".join([self.sql_template_path, + 'create.sql']), + data=resp_data, query_type="create") sql_header = """-- {0}: {1} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql index 62e61d3..efd7775 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/create.sql @@ -3,22 +3,33 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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 %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) - RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} - LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} +{% endif %} +) + RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} + LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql index 6976a75..63585ad 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/create.sql @@ -3,25 +3,37 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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 %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql index 102dc7a..45a9c61 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/create.sql @@ -3,26 +3,37 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data -.args %} +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 -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql index 62e61d3..efd7775 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/create.sql @@ -3,22 +3,33 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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 %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) - RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} - LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} +{% endif %} +) + RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} + LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} 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 9514344..516e6d4 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 @@ -3,25 +3,37 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE 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 %} +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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} - ) +{% if data.func_args %} +{% set func_args = data.func_args.split(',') %} + +{% for f in func_args %} + {{ f|trim }}{% if not loop.last %}, +{% endif %} +{% endfor %} + +{% endif %} +) RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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 %}{% endif %} AS {% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql index 102dc7a..b5b7176 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/create.sql @@ -3,8 +3,7 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data -.args %} +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 %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql index a971b55..0a8cb20 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/create.sql @@ -3,11 +3,12 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ 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 %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} +{% if not loop.last %}, {% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql index 2396517..073ce1d 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/create.sql @@ -3,10 +3,12 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ 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 %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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|qtIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} + {% if not loop.last %}, {% endif %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql index c5f0084..f1a40af 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/create.sql @@ -3,10 +3,13 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args %} +CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined or data.func_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 %} +{% endfor -%} +{% if data.func_args %}{{ data.func_args }}{% endif %} +){% endif %} +{% if query_type != 'create' %} {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% elif not data.proleakproof %} NOT LEAKPROOF {% endif %} {% if data.proisstrict %}STRICT {% endif %} @@ -17,7 +20,7 @@ CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% i ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %} SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%} -{% endif %} +{% endif %}{% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql index 8e51a49..6e9fd78 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/create.sql @@ -6,16 +6,19 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql index 149c204..7ca4bf1 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/create.sql @@ -6,6 +6,10 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proleakproof %}LEAKPROOF {% else %}NOT LEAKPROOF {% endif %} {% if data.proisstrict %}STRICT {% endif %} {% if data.prosecdef %}SECURITY DEFINER {% endif %} @@ -18,7 +22,7 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor %} {% endif %} -AS {% if data.lanname == 'c' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql index fa36ad8..5445855 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/create.sql @@ -3,22 +3,25 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} -CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() +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 query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql index 8e51a49..6e9fd78 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/create.sql @@ -6,16 +6,19 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} - {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% endif -%}{% if data.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} + {% if data.provolatile %}{{ data.provolatile }} {% endif %}{% if data.proisstrict %}STRICT {% endif %}{% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql index f7d497e..210cf3a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/create.sql @@ -6,19 +6,22 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} +AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql index fa36ad8..5c7c568 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/create.sql @@ -6,23 +6,26 @@ CREATE FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}() RETURNS{% if data.proretset %} SETOF{% endif %} {{ conn|qtTypeIdent(data.prorettypename) }} LANGUAGE {{ data.lanname|qtLiteral }} +{% if data.procost %} + COST {{data.procost}} +{% endif %} +{% if query_type != 'create' %} {% if data.provolatile %}{{ data.provolatile }} {% 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.procost %} - - COST {{data.procost}}{% endif %}{% if data.prorows %} +{% if data.proiswindow %}WINDOW{% 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' %} ++AS {% endif %}{% if data.lanname == 'c' %} {{ data.probin|qtLiteral }}, {{ data.prosrc_c|qtLiteral }} {% else %} $BODY$ -{{ data.prosrc }} +{{ data.prosrc }}; $BODY${% endif -%}; {% if data.funcowner %} ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re: [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node @ 2016-07-22 15:38 Dave Page <[email protected]> parent: Harshal Dhumal <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Dave Page @ 2016-07-22 15:38 UTC (permalink / raw) To: Harshal Dhumal <[email protected]>; +Cc: Surinder Kumar <[email protected]>; pgadmin-hackers Thanks, applied. On Fri, Jul 22, 2016 at 8:56 AM, Harshal Dhumal <[email protected]> wrote: > Hi, > > PFA rebased patch for RM1293 and RM1294. > > -- > Harshal Dhumal > Software Engineer > > EnterpriseDB India: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > On Mon, Jun 6, 2016 at 7:17 PM, Surinder Kumar > <[email protected]> wrote: >> >> Hi, >> >> Please find updated patch: >> Issue: Code changes are not consistent with the present code in >> functions/__init__.py, like don't rename variable func_def to func_defintion >> until it is required. >> Review comment given by Ashesh personally. >> >> Please review. >> >> On Fri, Jun 3, 2016 at 3:31 PM, Surinder Kumar >> <[email protected]> wrote: >>> >>> Hi, >>> >>> Please find attached patch for: >>> 1. RM#1293 - SQL pane is not displaying GRANT queries in functions node >>> 2. RM#1294 - Comments are not visible in sql pane for trigger functions >>> and functions >>> >>> Explanation: >>> Previously we we using 'get_defintion.sql' template to generate SQL for >>> SQL pane for functions, procedure & trigger functions node. >>> but GRANT statements and COMMENTS changes were missing. In order to fix >>> that, now we have used both 'create.sql' and 'get_definition.sql' templates >>> to generate full SQL query >>> for SQL pane. >>> >>> Please review. >>> >>> On Thu, Jun 2, 2016 at 7:32 PM, Surinder Kumar >>> <[email protected]> wrote: >>>> >>>> Please ignore this patch. I will send another patch. >>>> >>>> On Thu, Jun 2, 2016 at 7:25 PM, Surinder Kumar >>>> <[email protected]> wrote: >>>>> >>>>> Hi, >>>>> >>>>> Please find attached patch for RM#1293. >>>>> Add support to display GRANT privilege query in SQL Pane. >>>>> >>>>> Please review. >>>>> >>>>> Thanks, >>>>> Surinder Kumar >>>> >>>> >>> >> >> >> >> -- >> Sent via pgadmin-hackers mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgadmin-hackers >> > > > > -- > Sent via pgadmin-hackers mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgadmin-hackers > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgadmin-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-07-22 15:38 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-06-02 13:55 [pgAdmin4][Patch]: RM#1293 - SQL pane is not displaying GRANT queries in Function, Procedure and Trigger node Surinder Kumar <[email protected]> 2016-06-02 14:02 ` Surinder Kumar <[email protected]> 2016-06-03 10:01 ` Surinder Kumar <[email protected]> 2016-06-06 13:47 ` Surinder Kumar <[email protected]> 2016-07-22 07:56 ` Harshal Dhumal <[email protected]> 2016-07-22 15:38 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox