Subject: [pgAdmin4][Patch] To display proper SQL for Procedure node

From: Murtuza Zabuawala <[email-masked]>
Date: Tue, 30 May 2017 14:43:11 +0530

Hi,

PFA patch to fix to display procedure options like IMMUTABLE STRICT
SECURITY DEFINER PARALLEL RESTRICTED in SQL (Reverse Engineered).
RM#2280

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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 e292135..77f21b8 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
@@ -933,6 +933,7 @@ class FunctionView(PGChildNodeView, DataTypeReader):
         args_without_name = []
         cnt = 1
         args_list = []
+        vol_dict = {'v': 'VOLATILE', 's': 'STABLE', 'i': 'IMMUTABLE'}
 
         if 'arguments' in resp_data and len(resp_data['arguments']) > 0:
             args_list = resp_data['arguments']
@@ -961,6 +962,10 @@ class FunctionView(PGChildNodeView, DataTypeReader):
 
         if self.node_type == 'procedure':
             object_type = 'procedure'
+            if 'provolatile' in resp_data:
+                resp_data['provolatile'] = vol_dict.get(
+                    resp_data['provolatile'], ''
+                )
 
             # Get Schema Name from its OID.
             if 'pronamespace' in resp_data:
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 32249f6..b2ed406 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
@@ -11,10 +11,9 @@ CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% i
 {% if not loop.last %}, {% endif %}
 {% endfor -%}
 ){% endif %}
-{% endif %}
-{% if query_type != 'create' %}
 
-    {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% endif %}
+{% endif %}
+    {{ data.provolatile }} {% if data.proleakproof %}LEAKPROOF {% endif %}
 {% if data.proisstrict %}STRICT {% endif %}
 {% if data.prosecdef %}SECURITY DEFINER {% endif %}{% if data.procost %}
 
@@ -23,7 +22,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/procedure/ppas/sql/9.6_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/create.sql
index 4e02ad7..e8075f5 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/9.6_plus/create.sql
@@ -11,18 +11,20 @@ CREATE OR REPLACE PROCEDURE {{ conn|qtIdent(data.pronamespace, data.name) }}{% i
 {% if not loop.last %}, {% endif %}
 {% endfor -%}
 ){% endif %}
-{% endif %}
-{% if query_type != 'create' %}
 
-    {{ data.provolatile }}{% if data.proleakproof %} LEAKPROOF {% endif %}
+{% endif %}
+    {{ data.provolatile }} {% if data.proleakproof %}LEAKPROOF {% endif %}
 {% if data.proisstrict %}STRICT {% endif %}
 {% if data.prosecdef %}SECURITY DEFINER {% endif %}
 {% if data.proparallel and (data.proparallel == 'r' or data.proparallel == 's') %}
 {% if data.proparallel == 'r' %}PARALLEL RESTRICTED{% elif data.proparallel == 's' %}PARALLEL SAFE{% endif %}{% endif %}{% if data.procost %}
+
     COST {{data.procost}}{% endif %}{% if data.prorows %}
+
     ROWS {{data.prorows}}{% endif -%}{% if data.variables %}{% for v in data.variables %}
+
     SET {{ conn|qtIdent(v.name) }}={{ v.value|qtLiteral }}{% endfor -%}
-{% endif %}{% endif %}
+{% endif %}
 
 AS
 {{ data.prosrc }};

-- 
Sent via pgadmin-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers

---

From: Dave Page <[email-masked]>
Date: Mon, 5 Jun 2017 17:05:34 +0100

Thanks, patch applied.

On Tue, May 30, 2017 at 10:13 AM, Murtuza Zabuawala
<[email-masked]> wrote:
> Hi,
>
> PFA patch to fix to display procedure options like IMMUTABLE STRICT SECURITY
> DEFINER PARALLEL RESTRICTED in SQL (Reverse Engineered).
> RM#2280
>
> --
> Regards,
> Murtuza Zabuawala

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company