public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgadmin4][Patch]: PG/EPAS 11 fixes for functions, procedures and trigger functions
Date: Fri, 22 Jun 2018 18:46:36 +0530
Message-ID: <CANxoLDdUQDBS9Md6i3NnkModPw4c=2nbiLM=7mBvpv8uzv--dQ@mail.gmail.com> (raw)
Hi Hackers
Attached is the patch to fix following:
- RM #3421 Function is not displayed in grant wizard screen and message
displayed.
- RM #3422 procedure is not displayed in grant wizard screen
- PG/EPAS 11 function query for auto complete.
- Added support for functions, procedures and trigger function for EPAS
11.
- Fixed issue to expand table for EPAS 9.6 and below due to change in
backend support logic.
I have run the regression test on PG/EPAS: 9.6, 10, 11.
--
*Akshay Joshi*
*Sr. Software Architect *
*Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
Attachments:
[application/octet-stream] RM_3421_3422.patch (25.8K, 3-RM_3421_3422.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/coll_stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/coll_stats.sql
new file mode 100644
index 0000000..cc4f734
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.prokind IN ('f', 'w')
+ AND typname NOT IN ('trigger', 'event_trigger')
+ )
+ORDER BY funcname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/delete.sql
new file mode 100644
index 0000000..8a9a4cc
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/delete.sql
@@ -0,0 +1,22 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP FUNCTION {{ conn|qtIdent(nspname, name) }}{{func_args}}{% if cascade %} CASCADE{% endif %};
+{% endif %}
+
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/get_definition.sql
new file mode 100644
index 0000000..ab25dbf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') as
+ func_with_identity_arguments,
+ nspname,
+ pr.proname as proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') as func_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND pr.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/get_oid.sql
new file mode 100644
index 0000000..93a2107
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/get_oid.sql
@@ -0,0 +1,17 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace as nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/node.sql
new file mode 100644
index 0000000..baedbef
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/node.sql
@@ -0,0 +1,23 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') || ')' AS name,
+ lanname, pg_get_userbyid(proowner) AS funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pr.protype = '0'::char
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/properties.sql
new file mode 100644
index 0000000..e99d56b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/function/ppas/sql/11_plus/properties.sql
@@ -0,0 +1,32 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes) AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=pr.oid) AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname NOT IN ('trigger', 'event_trigger')
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/coll_stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/coll_stats.sql
new file mode 100644
index 0000000..5924679
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.prokind = 'p'::char
+ AND typname NOT IN ('trigger', 'event_trigger')
+ )
+ORDER BY funcname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/delete.sql
new file mode 100644
index 0000000..b4fcdc2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/delete.sql
@@ -0,0 +1,21 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind = 'p'::char
+ AND pronamespace = {{scid}}::oid
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP PROCEDURE {{ conn|qtIdent(nspname, name) }};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/get_definition.sql
new file mode 100644
index 0000000..4c2e9d9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') as
+ func_with_identity_arguments,
+ nspname,
+ pr.proname as proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') as func_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind = 'p'::char
+ AND pronamespace = {{scid}}::oid
+ AND pr.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/get_oid.sql
new file mode 100644
index 0000000..be98b53
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/get_oid.sql
@@ -0,0 +1,17 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace AS nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ pr.prokind = 'p'::char
+ AND typname NOT IN ('trigger', 'event_trigger')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/node.sql
new file mode 100644
index 0000000..89dc768
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/node.sql
@@ -0,0 +1,29 @@
+SELECT
+ pr.oid,
+ CASE WHEN
+ pg_catalog.pg_get_function_identity_arguments(pr.oid) <> ''
+ THEN
+ pr.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')'
+ ELSE
+ pr.proname
+ END AS name,
+ lanname, pg_get_userbyid(proowner) AS funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind = 'p'::char
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname NOT IN ('trigger', 'event_trigger')
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/properties.sql
new file mode 100644
index 0000000..1763fd8
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedure/ppas/sql/11_plus/properties.sql
@@ -0,0 +1,39 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes) AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (CASE WHEN
+ pg_catalog.pg_get_function_identity_arguments(pr.oid) <> ''
+ THEN
+ pr.proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ')'
+ ELSE
+ pr.proname
+ END) AS name_with_args,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=pr.oid) AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind = 'p'::char
+ AND typname NOT IN ('trigger', 'event_trigger')
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/coll_stats.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/coll_stats.sql
new file mode 100644
index 0000000..cd8cecd
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/coll_stats.sql
@@ -0,0 +1,20 @@
+SELECT
+ funcname AS {{ conn|qtIdent(_('Name')) }},
+ calls AS {{ conn|qtIdent(_('Number of calls')) }},
+ total_time AS {{ conn|qtIdent(_('Total time')) }},
+ self_time AS {{ conn|qtIdent(_('Self time')) }}
+FROM
+ pg_stat_user_functions
+WHERE
+ schemaname = {{schema_name|qtLiteral}}
+ AND funcid IN (
+ SELECT p.oid
+ FROM
+ pg_proc p
+ JOIN
+ pg_type typ ON typ.oid=p.prorettype
+ WHERE
+ p.prokind IN ('f', 'w')
+ AND typname = 'trigger'
+ )
+ORDER BY funcname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/delete.sql
new file mode 100644
index 0000000..a2d698b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/delete.sql
@@ -0,0 +1,21 @@
+{% if scid and fnid %}
+SELECT
+ pr.proname as name, '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as func_args,
+ nspname
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND typname = 'trigger'
+ AND pr.oid = {{fnid}};
+{% endif %}
+
+{% if name %}
+DROP FUNCTION {{ conn|qtIdent(nspname, name) }}{{func_args}}{% if cascade %} CASCADE{% endif %};
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/get_definition.sql
new file mode 100644
index 0000000..ab25dbf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/get_definition.sql
@@ -0,0 +1,15 @@
+SELECT
+ pg_get_functiondef({{fnid}}::oid) AS func_def,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pr.oid), '') as
+ func_with_identity_arguments,
+ nspname,
+ pr.proname as proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pr.oid), '') as func_args
+FROM
+ pg_proc pr
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND pronamespace = {{scid}}::oid
+ AND pr.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/get_oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/get_oid.sql
new file mode 100644
index 0000000..413df91
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/get_oid.sql
@@ -0,0 +1,18 @@
+SELECT
+ pr.oid, pr.proname || '(' || COALESCE(pg_catalog
+ .pg_get_function_identity_arguments(pr.oid), '') || ')' as name,
+ lanname, pg_get_userbyid(proowner) as funcowner, pr.pronamespace AS nsp
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+JOIN
+ pg_namespace nsp ON nsp.oid=pr.pronamespace
+ AND nsp.nspname={{ nspname|qtLiteral }}
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname IN ('trigger', 'event_trigger')
+ AND lanname NOT IN ('edbspl', 'sql', 'internal')
+ AND pr.proname = {{ name|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/node.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/node.sql
new file mode 100644
index 0000000..592ef75
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/node.sql
@@ -0,0 +1,22 @@
+SELECT
+ pr.oid, pr.proname || '()' AS name,
+ lanname, pg_get_userbyid(proowner) AS funcowner, description
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+{% if fnid %}
+ AND pr.oid = {{ fnid|qtLiteral }}
+{% endif %}
+{% if scid %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ AND typname = 'trigger' AND lanname != 'edbspl'
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/properties.sql
new file mode 100644
index 0000000..7fc529b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_function/ppas/sql/11_plus/properties.sql
@@ -0,0 +1,32 @@
+SELECT
+ pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
+ pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ typns.nspname AS typnsp, lanname, proargnames, oidvectortypes(proargtypes) AS proargtypenames,
+ pg_get_expr(proargdefaults, 'pg_catalog.pg_class'::regclass) AS proargdefaultvals,
+ pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (SELECT
+ array_agg(provider || '=' || label)
+ FROM
+ pg_seclabel sl1
+ WHERE
+ sl1.objoid=pr.oid) AS seclabels
+FROM
+ pg_proc pr
+JOIN
+ pg_type typ ON typ.oid=prorettype
+JOIN
+ pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN
+ pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pr.prokind IN ('f', 'w')
+ AND typname = 'trigger' AND lanname != 'edbspl'
+{% if fnid %}
+ AND pr.oid = {{fnid}}::oid
+{% else %}
+ AND pronamespace = {{scid}}::oid
+{% endif %}
+ORDER BY
+ proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
index b1031d8..41b5336 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py
@@ -81,6 +81,8 @@ class PartitionsModule(CollectionNodeModule):
super(PartitionsModule, self).__init__(*args, **kwargs)
self.min_ver = 100000
self.max_ver = None
+ self.min_ppasver = 100000
+ self.max_ppasver = None
def get_nodes(self, gid, sid, did, scid, **kwargs):
"""
diff --git a/web/pgadmin/misc/templates/sqlautocomplete/sql/11_plus/functions.sql b/web/pgadmin/misc/templates/sqlautocomplete/sql/11_plus/functions.sql
new file mode 100644
index 0000000..1190ad0
--- /dev/null
+++ b/web/pgadmin/misc/templates/sqlautocomplete/sql/11_plus/functions.sql
@@ -0,0 +1,30 @@
+{# ============= Fetch the list of functions based on given schema_names ============= #}
+{% if func_name %}
+SELECT n.nspname schema_name,
+ p.proname func_name,
+ pg_catalog.pg_get_function_arguments(p.oid) arg_list,
+ pg_catalog.pg_get_function_result(p.oid) return_type,
+ CASE WHEN p.prokind = 'a' THEN true ELSE false END is_aggregate,
+ CASE WHEN p.prokind = 'w' THEN true ELSE false END is_window,
+ p.proretset is_set_returning
+FROM pg_catalog.pg_proc p
+ INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
+ WHERE n.nspname = '{{schema_name}}' AND p.proname = '{{func_name}}'
+ AND p.proretset
+ ORDER BY 1, 2
+{% else %}
+SELECT n.nspname schema_name,
+ p.proname object_name,
+ pg_catalog.pg_get_function_arguments(p.oid) arg_list,
+ pg_catalog.pg_get_function_result(p.oid) return_type,
+ CASE WHEN p.prokind = 'a' THEN true ELSE false END is_aggregate,
+ CASE WHEN p.prokind = 'w' THEN true ELSE false END is_window,
+ p.proretset is_set_returning
+FROM pg_catalog.pg_proc p
+ INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
+ WHERE n.nspname IN ({{schema_names}})
+{% if is_set_returning %}
+ AND p.proretset
+{% endif %}
+ ORDER BY 1, 2
+{% endif %}
diff --git a/web/pgadmin/tools/grant_wizard/__init__.py b/web/pgadmin/tools/grant_wizard/__init__.py
index e695d7b..e00153c 100644
--- a/web/pgadmin/tools/grant_wizard/__init__.py
+++ b/web/pgadmin/tools/grant_wizard/__init__.py
@@ -242,7 +242,9 @@ def properties(sid, did, node_id, node_type):
# Fetch procedures only if server type is ppas
if (len(server_prop) > 0 and
- server_prop['server_type'] == 'ppas' and
+ (server_prop['server_type'] == 'ppas' or
+ (server_prop['server_type'] == 'pg' and
+ server_prop['version'] >= 11000)) and
ntype in ['schema', 'procedure']):
SQL = render_template("/".join(
[server_prop['template_path'], '/sql/function.sql']),
diff --git a/web/pgadmin/tools/grant_wizard/templates/grant_wizard/pg/11_plus/sql/function.sql b/web/pgadmin/tools/grant_wizard/templates/grant_wizard/pg/11_plus/sql/function.sql
new file mode 100644
index 0000000..32d9b25
--- /dev/null
+++ b/web/pgadmin/tools/grant_wizard/templates/grant_wizard/pg/11_plus/sql/function.sql
@@ -0,0 +1,26 @@
+{# ===== Fetch list of Database object types(Functions) ====== #}
+{% if type and node_id %}
+{% set func_type = 'Trigger Function' if type == 'trigger_function' else 'Procedure' if type == 'procedure' else 'Function' %}
+{% set icon = 'icon-function' if type == 'function' else 'icon-procedure' if type == 'procedure' else 'icon-trigger_function' %}
+{% set kind = 'p' if type == 'procedure' else 'f' %}
+SELECT
+ pr.oid,
+ pg_get_function_identity_arguments(pr.oid) AS proargs,
+ pr.proname AS name,
+ nsp.nspname AS nspname,
+ '{{ func_type }}' AS object_type,
+ '{{ icon }}' AS icon
+FROM
+ pg_proc pr
+JOIN pg_namespace nsp ON nsp.oid=pr.pronamespace
+JOIN pg_type typ ON typ.oid=prorettype
+JOIN pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pronamespace = {{ node_id }}::oid
+ AND typname {{ 'NOT' if type != 'trigger_function' else '' }} IN ('trigger', 'event_trigger')
+ AND pr.prokind = '{{ kind }}'
+ORDER BY
+ proname
+{% endif %}
diff --git a/web/pgadmin/tools/grant_wizard/templates/grant_wizard/ppas/11_plus/sql/function.sql b/web/pgadmin/tools/grant_wizard/templates/grant_wizard/ppas/11_plus/sql/function.sql
new file mode 100644
index 0000000..32d9b25
--- /dev/null
+++ b/web/pgadmin/tools/grant_wizard/templates/grant_wizard/ppas/11_plus/sql/function.sql
@@ -0,0 +1,26 @@
+{# ===== Fetch list of Database object types(Functions) ====== #}
+{% if type and node_id %}
+{% set func_type = 'Trigger Function' if type == 'trigger_function' else 'Procedure' if type == 'procedure' else 'Function' %}
+{% set icon = 'icon-function' if type == 'function' else 'icon-procedure' if type == 'procedure' else 'icon-trigger_function' %}
+{% set kind = 'p' if type == 'procedure' else 'f' %}
+SELECT
+ pr.oid,
+ pg_get_function_identity_arguments(pr.oid) AS proargs,
+ pr.proname AS name,
+ nsp.nspname AS nspname,
+ '{{ func_type }}' AS object_type,
+ '{{ icon }}' AS icon
+FROM
+ pg_proc pr
+JOIN pg_namespace nsp ON nsp.oid=pr.pronamespace
+JOIN pg_type typ ON typ.oid=prorettype
+JOIN pg_namespace typns ON typns.oid=typ.typnamespace
+JOIN pg_language lng ON lng.oid=prolang
+LEFT OUTER JOIN pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass)
+WHERE
+ pronamespace = {{ node_id }}::oid
+ AND typname {{ 'NOT' if type != 'trigger_function' else '' }} IN ('trigger', 'event_trigger')
+ AND pr.prokind = '{{ kind }}'
+ORDER BY
+ proname
+{% endif %}
view thread (2+ 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]: PG/EPAS 11 fixes for functions, procedures and trigger functions
In-Reply-To: <CANxoLDdUQDBS9Md6i3NnkModPw4c=2nbiLM=7mBvpv8uzv--dQ@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