public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin4][Patch] - RM 3850 - EXEC script generates incorrect syntax: ERROR: syntax error at or near
Date: Wed, 23 Jan 2019 12:59:03 +0530
Message-ID: <CAFOhELd=7C967ViQ7x53Uw58ngYv1C0Dm6Y7r3-EKRdvcJMGtg@mail.gmail.com> (raw)
Hi,
Please find the attached patch to fix the RM 3850 - EXEC script generates
incorrect syntax: ERROR: syntax error at or near.
Thanks,
Khushboo
Attachments:
[application/octet-stream] RM_3850.patch (8.2K, 3-RM_3850.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 a6c7e350..fda1cf87 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
@@ -1508,16 +1508,20 @@ class FunctionView(PGChildNodeView, DataTypeReader):
name = resp_data['pronamespace'] + "." + resp_data['name_with_args']
# Fetch only arguments
- args = name[name.rfind('('):].strip('(').strip(')').split(',')
- # Remove unwanted spaces from arguments
- args = [arg.strip(' ') for arg in args]
+ if name.rfind('(') != -1:
+ args = name[name.rfind('('):].strip('(').strip(')').split(',')
+ # Remove unwanted spaces from arguments
+ args = [arg.strip(' ') for arg in args]
- # Remove duplicate and then format arguments
- for arg in list(set(args)):
- formatted_arg = '\n\t<' + arg + '>'
- name = name.replace(arg, formatted_arg)
+ # Remove duplicate and then format arguments
+ for arg in list(set(args)):
+ formatted_arg = '\n\t<' + arg + '>'
+ name = name.replace(arg, formatted_arg)
+
+ name = name.replace(')', '\n)')
+ else:
+ name += '()'
- name = name.replace(')', '\n)')
if self.manager.server_type == 'pg':
sql = "CALL {0}".format(name)
else:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_exec_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_exec_sql.py
new file mode 100644
index 00000000..4424b4a8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/test_procedure_exec_sql.py
@@ -0,0 +1,81 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+import uuid
+import re
+
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from . import utils as funcs_utils
+
+
+class ProcedureExecSQLTestCase(BaseTestGenerator):
+ """ This class will check the EXEC SQL for Procedure. """
+ skip_on_database = ['gpdb']
+ scenarios = [
+ # Fetching procedure SQL to EXEC.
+ ('Fetch Procedure SQL to execute', dict(
+ url='/browser/procedure/exec_sql/', with_args=False, args="",
+ expected_sql="{0} {1}.{2}()")),
+ ('Fetch Procedure with arguuments SQL to execute', dict(
+ url='/browser/procedure/exec_sql/', with_args=True,
+ args="arg1 bigint",
+ expected_sql="{0} {1}.{2}( <arg1 bigint> )"))
+ ]
+
+ def runTest(self):
+ """ This function will check the EXEC SQL. """
+ super(ProcedureExecSQLTestCase, self).setUp()
+ self = funcs_utils.set_up(self)
+
+ if self.server_type == "pg" and\
+ self.server_version < 110000:
+ message = "Procedures are not supported by PG < 110000."
+ self.skipTest(message)
+
+ proc_name = "test_procedure_exec_sql_%s" % str(uuid.uuid4())[1:8]
+ proc_info = funcs_utils.create_procedure(
+ self.server, self.db_name, self.schema_name, proc_name,
+ self.server_type, self.server_version, self.with_args, self.args)
+
+ proc_id = proc_info[0]
+
+ exec_response = self.tester.get(
+ self.url + str(utils.SERVER_GROUP) +
+ '/' + str(self.server_id) + '/' + str(self.db_id) + '/' +
+ str(self.schema_id) + '/' +
+ str(proc_id))
+ self.assertEquals(exec_response.status_code, 200)
+ exec_sql = json.loads(exec_response.data.decode('utf-8'))
+
+ # Replace multiple spaces with one space and check the expected sql
+ sql = re.sub('\s+', ' ', exec_sql).strip()
+
+ # Verify the expected EXEC SQL
+ if self.server_type == "pg":
+ expected_sql = self.expected_sql.format("CALL", self.schema_name,
+ proc_name)
+ else:
+ expected_sql = self.expected_sql.format("EXEC", self.schema_name,
+ proc_name)
+
+ self.assertEquals(sql, expected_sql)
+
+ # Verify the EXEC SQL by running it if we don't have arguments
+ if not self.with_args:
+ funcs_utils.execute_procedure(self.server, self.db_name, exec_sql)
+
+ # Disconnect the database
+ database_utils.disconnect_database(self, self.server_id, self.db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py
index 18ba54a3..96948859 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/tests/utils.py
@@ -100,7 +100,7 @@ def verify_trigger_function(server, db_name, func_name):
def create_procedure(server, db_name, schema_name, func_name, s_type,
- s_version):
+ s_version, with_args=False, args=""):
"""This function add the procedure to schema"""
try:
connection = utils.get_db_connection(db_name,
@@ -111,22 +111,24 @@ def create_procedure(server, db_name, schema_name, func_name, s_type,
server['sslmode'])
pg_cursor = connection.cursor()
if s_type == 'pg':
- query = "CREATE PROCEDURE " + schema_name + "." + func_name + \
- "()" \
- " LANGUAGE 'sql'" \
- " SECURITY DEFINER AS $$" \
- " SELECT 1; $$;"
+ query = "CREATE PROCEDURE {0}.{1}" \
+ "({2})" \
+ " LANGUAGE 'sql'" \
+ " SECURITY DEFINER AS $$" \
+ " SELECT 1; $$;".format(schema_name, func_name, args)
else:
if s_version >= 90500:
- query = "CREATE PROCEDURE " + schema_name + "." + func_name + \
- "()" \
+ query = "CREATE PROCEDURE {0}.{1}" \
+ "({2})" \
" SECURITY DEFINER AS $BODY$ BEGIN" \
- " NULL; END; $BODY$"
+ " NULL; END; $BODY$".format(schema_name, func_name,
+ args)
else:
- query = "CREATE PROCEDURE " + schema_name + "." + func_name + \
- "()" \
+ query = "CREATE PROCEDURE {0}.{1}" \
+ "({2})" \
" AS $BODY$ BEGIN" \
- " NULL; END; $BODY$"
+ " NULL; END; $BODY$".format(schema_name, func_name,
+ args)
pg_cursor.execute(query)
connection.commit()
@@ -220,3 +222,16 @@ def set_up(obj):
raise Exception("Could not find the schema.")
return obj
+
+
+def execute_procedure(server, db_name, proc_exec_sql):
+ """This function verifies the procedure in db"""
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'],
+ server['sslmode'])
+ pg_cursor = connection.cursor()
+ pg_cursor.execute(proc_exec_sql)
+ connection.close()
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgAdmin4][Patch] - RM 3850 - EXEC script generates incorrect syntax: ERROR: syntax error at or near
In-Reply-To: <CAFOhELd=7C967ViQ7x53Uw58ngYv1C0Dm6Y7r3-EKRdvcJMGtg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox