public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect 4+ messages / 2 participants [nested] [flat]
* [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect @ 2016-09-13 11:41 Surinder Kumar <[email protected]> 2016-09-14 10:00 ` Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Dave Page <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Surinder Kumar @ 2016-09-13 11:41 UTC (permalink / raw) To: pgadmin-hackers Hi *Patch with following changes:* 1) In SQL pane, the signature in comments had arguments with name and type, Now it will show arguments with type only. 2) There is no indent+newline between arguments (there isn't even a space). - Fixed 3) The default value for the last argument is omitted. - Fixed Last argument is omitted because the value for the last argument we got from table is empty. Now we form function signature in the *get_definition.sql *using *'* *pg_catalog.pg_get_function_arguments*' method. Above changes will reflect in Functions, Procedures & Trigger function Nodes. Please review attached patch. 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] RM1674.patch (33.5K, 3-RM1674.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 f269dcb..d11c0a0 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 @@ -938,18 +938,42 @@ class FunctionView(PGChildNodeView, DataTypeReader): if self.node_type == 'procedure': object_type = 'procedure' - # Get SQL to create Function - status, func_def = self._get_sql(gid, sid, did, scid, resp_data, - None, True) + # Get Schema Name from its OID. + if 'pronamespace' in resp_data: + resp_data['pronamespace'] = self._get_schema(resp_data[ + 'pronamespace']) + + SQL = render_template("/".join([self.sql_template_path, + '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=func_def) + return internal_server_error(errormsg=res) - name = resp_data['pronamespace'] + "." + resp_data['name_with_args'] + # Add newline and tab before each argument to format + name_with_default_args = res['rows'][0]['name_with_default_args'].replace(',', ',\r\t') - # Create mode + # Parse privilege data + if 'acl' in resp_data: + resp_data['acl'] = parse_priv_to_db(resp_data['acl'], ['X']) + + # generate function signature + header_func_name = '{0}.{1}({2})'.format( + resp_data['pronamespace'], + resp_data['proname'], + resp_data['proargtypenames'] + ) + + # Generate sql for "SQL panel" + # func_def is procedure signature with default arguments + # query_for - To distinguish the type of call func_def = render_template("/".join([self.sql_template_path, 'create.sql']), - data=resp_data, query_type="create") + data=resp_data, query_type="create", + func_def=name_with_default_args, + query_for="sql_panel") else: object_type = 'function' @@ -962,7 +986,13 @@ class FunctionView(PGChildNodeView, DataTypeReader): if 'acl' in resp_data: resp_data['acl'] = parse_priv_to_db(resp_data['acl'], ['X']) - # Create mode + # generate function signature + header_func_name = '{0}.{1}({2})'.format( + resp_data['pronamespace'], + resp_data['proname'], + resp_data['proargtypenames'] + ) + SQL = render_template("/".join([self.sql_template_path, 'get_definition.sql'] ), data=resp_data, @@ -972,8 +1002,9 @@ class FunctionView(PGChildNodeView, DataTypeReader): if not status: return internal_server_error(errormsg=res) - name = res['rows'][0]['name'] - # Create mode + # Add newline and tab before each argument to format + name_with_default_args = res['rows'][0]['name_with_default_args'].replace(',', ',\r\t') + if hasattr(str, 'decode'): if resp_data['prosrc']: resp_data['prosrc'] = resp_data['prosrc'].decode( @@ -983,15 +1014,21 @@ class FunctionView(PGChildNodeView, DataTypeReader): resp_data['prosrc_c'] = resp_data['prosrc_c'].decode( 'utf-8' ) + + # Generate sql for "SQL panel" + # func_def is function signature with default arguments + # query_for - To distinguish the type of call func_def = render_template("/".join([self.sql_template_path, 'create.sql']), - data=resp_data, query_type="create") + data=resp_data, query_type="create", + func_def=name_with_default_args, + query_for="sql_panel") sql_header = """-- {0}: {1} -- DROP {0} {1}; -""".format(object_type.upper(), name) +""".format(object_type.upper(), header_func_name) if hasattr(str, 'decode'): sql_header = sql_header.decode('utf-8') 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 5b62e95..db19628 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,12 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}} +{% else %} CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.args %} {% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}}{% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} ) +{% endif -%} RETURNS{% if data.proretset and data.prorettypename.startswith('SETOF ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %} LANGUAGE {{ data.lanname|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.1_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 3cbab1c..11871ef 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,12 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}} +{% else %} CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.args %} {% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} ) +{% endif -%} RETURNS{% if data.proretset and data.prorettypename.startswith('SETOF ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %} LANGUAGE {{ data.lanname|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.2_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 f27f526..2d73416 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,12 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}} +{% else %} CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.args %} {% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname) }} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} -{% if not loop.last %},{% endif %} +{% if not loop.last %}, {% endif %} {% endfor %} {% endif -%} ) +{% endif %} RETURNS{% if data.proretset and data.prorettypename.startswith('SETOF ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %} LANGUAGE {{ data.lanname|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/pg/sql/9.5_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 e5d5894..66a4221 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,12 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}} +{% else %} CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.args %} {% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} ) +{% endif -%} RETURNS{% if data.proretset and data.prorettypename.startswith('SETOF ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %} LANGUAGE {{ data.lanname|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.1_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 84242b9..fa87e4a 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,12 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}} +{% else %} CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.args %} {% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} ) +{% endif -%} RETURNS{% if data.proretset and data.prorettypename.startswith('SETOF ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %} LANGUAGE {{ data.lanname|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.2_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 4386b32..33441f3 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,12 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{func_def}} +{% else %} CREATE{% if query_type is defined %}{{' OR REPLACE'}}{% endif %} FUNCTION {{ conn|qtIdent(data.pronamespace, data.name) }}({% if data.args %} {% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname) }} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %},{% endif %} {% endfor %} {% endif -%} ) +{% endif -%} RETURNS{% if data.proretset and data.prorettypename.startswith('SETOF ') %} {{ data.prorettypename }} {% elif data.proretset %} SETOF {{ conn|qtTypeIdent(data.prorettypename) }}{% else %} {{ conn|qtTypeIdent(data.prorettypename) }}{% endif %} LANGUAGE {{ data.lanname|qtLiteral }} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/9.5_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 235f9c6..f7ef9c4 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,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE OR REPLACE PROCEDURE {{func_def}} +{% else %} CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined %} ({% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} {% endfor -%} -){% endif %} +{% endif %} +) +{% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/get_definition.sql index c635f1e..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.1_plus/get_definition.sql @@ -1,7 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog - .pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 3a211f2..51e4fa7 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,11 +3,16 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE OR REPLACE PROCEDURE {{func_def}} +{% else %} CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined %} ({% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} {% endfor -%} -){% endif %} +{% endif %} +) +{% endif %} AS {{ data.prosrc }}; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/get_definition.sql index c635f1e..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.2_plus/get_definition.sql @@ -1,7 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog - .pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN 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 d0682aa..f0469d9 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,11 +3,15 @@ {% import 'macros/functions/variable.macros' as VARIABLE %} {% set is_columns = [] %} {% if data %} +{% if query_for == 'sql_panel' and func_def is defined %} +CREATE OR REPLACE PROCEDURE {{func_def}} +{% else %} CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% if data.args is defined %} ({% for p in data.args %}{% if p.argmode %}{{p.argmode}} {% endif %}{% if p.argname %}{{ conn|qtIdent(p.argname)}} {% endif %}{% if p.argtype %}{{ conn|qtTypeIdent(p.argtype) }}{% endif %}{% if p.argdefval %} DEFAULT {{p.argdefval}}{% endif %} {% if not loop.last %}, {% endif %} {% endfor -%} ){% endif %} +{% endif %} {% if query_type != 'create' %} {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% elif not data.proleakproof %} NOT LEAKPROOF {% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/get_definition.sql index c635f1e..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.5_plus/get_definition.sql @@ -1,7 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog - .pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.1_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.2_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/pg/sql/9.5_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.1_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.2_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/get_definition.sql index 589ecce..07f6a2c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/get_definition.sql +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/9.5_plus/get_definition.sql @@ -1,6 +1,7 @@ SELECT pg_get_functiondef({{fnid}}::oid) AS func_def, - nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' as name, + nspname || '.' || pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') || ')' as name_with_default_args FROM pg_proc pr JOIN ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect 2016-09-13 11:41 [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Surinder Kumar <[email protected]> @ 2016-09-14 10:00 ` Dave Page <[email protected]> 2016-09-14 10:23 ` Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Surinder Kumar <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Dave Page @ 2016-09-14 10:00 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers Thanks - committed with a couple of minor formatting tweaks: - Remove the leading space before arguments (e.g. s/, /\r\t/ instead of s/,/\r\t/) - Put the first argument on a new line as well. Please double-check I didn't break anything that I've missed! On Tue, Sep 13, 2016 at 12:41 PM, Surinder Kumar <[email protected]> wrote: > Hi > > Patch with following changes: > > 1) In SQL pane, the signature in comments had arguments with name and type, > Now it will show arguments with type only. > > 2) There is no indent+newline between arguments (there isn't even a space). > - Fixed > > 3) The default value for the last argument is omitted. - Fixed > Last argument is omitted because the value for the last argument we got from > table is empty. > Now we form function signature in the get_definition.sql using > 'pg_catalog.pg_get_function_arguments' method. > > Above changes will reflect in Functions, Procedures & Trigger function > Nodes. > > Please review attached patch. > > Thanks, > Surinder Kumar > > > -- > 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] 4+ messages in thread
* Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect 2016-09-13 11:41 [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Surinder Kumar <[email protected]> 2016-09-14 10:00 ` Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Dave Page <[email protected]> @ 2016-09-14 10:23 ` Surinder Kumar <[email protected]> 2016-09-14 10:25 ` Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Dave Page <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Surinder Kumar @ 2016-09-14 10:23 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers On Wed, Sep 14, 2016 at 3:30 PM, Dave Page <[email protected]> wrote: > Thanks - committed with a couple of minor formatting tweaks: > > - Remove the leading space before arguments (e.g. s/, /\r\t/ instead > of s/,/\r\t/) > - Put the first argument on a new line as well. βIt seems fine except the case when there are no arguments, the closing bracket ends in next line. Is it ok? > > Please double-check I didn't break anything that I've missed! > It doesn't break anything β.β > > On Tue, Sep 13, 2016 at 12:41 PM, Surinder Kumar > <[email protected]> wrote: > > Hi > > > > Patch with following changes: > > > > 1) In SQL pane, the signature in comments had arguments with name and > type, > > Now it will show arguments with type only. > > > > 2) There is no indent+newline between arguments (there isn't even a > space). > > - Fixed > > > > 3) The default value for the last argument is omitted. - Fixed > > Last argument is omitted because the value for the last argument we got > from > > table is empty. > > Now we form function signature in the get_definition.sql using > > 'pg_catalog.pg_get_function_arguments' method. > > > > Above changes will reflect in Functions, Procedures & Trigger function > > Nodes. > > > > Please review attached patch. > > > > Thanks, > > Surinder Kumar > > > > > > -- > > 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 > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect 2016-09-13 11:41 [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Surinder Kumar <[email protected]> 2016-09-14 10:00 ` Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Dave Page <[email protected]> 2016-09-14 10:23 ` Re: [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Surinder Kumar <[email protected]> @ 2016-09-14 10:25 ` Dave Page <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Dave Page @ 2016-09-14 10:25 UTC (permalink / raw) To: Surinder Kumar <[email protected]>; +Cc: pgadmin-hackers On Wed, Sep 14, 2016 at 11:23 AM, Surinder Kumar <[email protected]> wrote: > On Wed, Sep 14, 2016 at 3:30 PM, Dave Page <[email protected]> wrote: >> >> Thanks - committed with a couple of minor formatting tweaks: >> >> - Remove the leading space before arguments (e.g. s/, /\r\t/ instead >> of s/,/\r\t/) >> - Put the first argument on a new line as well. > > It seems fine except the case when there are no arguments, the closing > bracket ends in next line. Is it ok? I'm not overly upset by it :-). If it irks you, feel free to change it! >> >> >> Please double-check I didn't break anything that I've missed! > > It doesn't break anything Cool, thanks. >> >> On Tue, Sep 13, 2016 at 12:41 PM, Surinder Kumar >> <[email protected]> wrote: >> > Hi >> > >> > Patch with following changes: >> > >> > 1) In SQL pane, the signature in comments had arguments with name and >> > type, >> > Now it will show arguments with type only. >> > >> > 2) There is no indent+newline between arguments (there isn't even a >> > space). >> > - Fixed >> > >> > 3) The default value for the last argument is omitted. - Fixed >> > Last argument is omitted because the value for the last argument we got >> > from >> > table is empty. >> > Now we form function signature in the get_definition.sql using >> > 'pg_catalog.pg_get_function_arguments' method. >> > >> > Above changes will reflect in Functions, Procedures & Trigger function >> > Nodes. >> > >> > Please review attached patch. >> > >> > Thanks, >> > Surinder Kumar >> > >> > >> > -- >> > 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 > > -- 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] 4+ messages in thread
end of thread, other threads:[~2016-09-14 10:25 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-09-13 11:41 [pgAdmin4][Patch]: RM1674 - Reverse engineered SQL for functions with default values is incorrect Surinder Kumar <[email protected]> 2016-09-14 10:00 ` Dave Page <[email protected]> 2016-09-14 10:23 ` Surinder Kumar <[email protected]> 2016-09-14 10:25 ` 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