public inbox for [email protected]
help / color / mirror / Atom feedFrom: Aditya Toshniwal <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin][RM5024] Refactoring SQL of Functions and Procedures.
Date: Tue, 24 Dec 2019 12:56:45 +0530
Message-ID: <CAM9w-_ndX+nnVocX=OqrvgBWpu7vqCNzd+ODYCGN99U569QCqQ@mail.gmail.com> (raw)
Hi Hackers,
Attached is the patch to refactor SQL for functions and procedures wherever
possible.
Along with this, it also fixes a bug where functions dialog data types was
showing system objects when "Show system objects" was set to False and
vice-versa.
Kindly review.
--
Thanks and Regards,
Aditya Toshniwal
pgAdmin Hacker | Sr. Software Engineer | EnterpriseDB India | Pune
"Don't Complain about Heat, Plant a TREE"
Attachments:
[application/octet-stream] RM5024.patch (35.3K, 3-RM5024.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 a66e2215d..40d5000b4 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
@@ -654,9 +654,10 @@ class FunctionView(PGChildNodeView, DataTypeReader):
condition = "(typtype IN ('b', 'c', 'd', 'e', 'p', 'r') AND " \
"typname NOT IN ('any', 'trigger', 'language_handler', " \
"'event_trigger'))"
- if self.blueprint.show_system_objects:
+ if not self.blueprint.show_system_objects:
condition += " AND nspname NOT LIKE E'pg\\\\_toast%' AND " \
- "nspname NOT LIKE E'pg\\\\_temp%'"
+ "nspname NOT LIKE E'pg\\\\_temp%' AND "\
+ "nspname != 'information_schema'"
# Get Types
status, types = self.get_types(self.conn, condition, False, scid)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/11_plus/properties.sql
index 50c2ca041..6b2cb2fa9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/11_plus/properties.sql
@@ -1,10 +1,13 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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 pr.prokind = 'w' THEN TRUE ELSE FALSE END AS proiswindow,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -20,14 +23,14 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
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 %}
- AND typname NOT IN ('trigger', 'event_trigger')
ORDER BY
proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/12_plus/properties.sql
index e28d46b7c..7d698267f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/12_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/12_plus/properties.sql
@@ -1,10 +1,13 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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 pr.prokind = 'w' THEN TRUE ELSE FALSE END AS proiswindow,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
CASE WHEN prosupport = 0::oid THEN ''
ELSE (
SELECT quote_ident(nspname) || '.' || quote_ident(proname) AS tfunctions
@@ -27,14 +30,14 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
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 %}
- AND typname NOT IN ('trigger', 'event_trigger')
ORDER BY
proname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.2_plus/properties.sql
index ef6ae0296..ebed10505 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.2_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.2_plus/properties.sql
@@ -1,9 +1,12 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -19,7 +22,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.5_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.5_plus/properties.sql
index b5f5c5d8b..9cc41721d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.5_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/9.5_plus/properties.sql
@@ -1,9 +1,12 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -19,7 +22,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
{% if fnid %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/default/properties.sql
index b5f5c5d8b..ebed10505 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/pg/sql/default/properties.sql
@@ -1,9 +1,12 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -19,14 +22,14 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
+ AND typname NOT IN ('trigger', 'event_trigger')
{% if fnid %}
AND pr.oid = {{fnid}}::oid
{% else %}
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/functions/ppas/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/11_plus/properties.sql
index e99d56b74..6b2cb2fa9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/11_plus/properties.sql
@@ -1,9 +1,13 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -19,7 +23,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
pr.prokind IN ('f', 'w')
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/12_plus/properties.sql
index 1bb8c934c..9eac4807d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/12_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/12_plus/properties.sql
@@ -1,9 +1,13 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
CASE WHEN prosupport = 0::oid THEN '' ELSE prosupport::text END AS prosupportfunc,
(SELECT
array_agg(provider || '=' || label)
@@ -20,7 +24,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
pr.prokind IN ('f', 'w')
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/default/properties.sql
index ef6ae0296..ebed10505 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/ppas/sql/default/properties.sql
@@ -1,9 +1,12 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -19,7 +22,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/pg/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/pg/sql/11_plus/properties.sql
index 1a6c010c3..7efbad8e4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/pg/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/pg/sql/11_plus/properties.sql
@@ -1,16 +1,24 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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::text
- END) AS name_with_args,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (
+ WITH name_with_args_tab AS (SELECT pg_catalog.pg_get_function_identity_arguments(pr.oid) AS val)
+ SELECT CASE WHEN
+ val <> ''
+ THEN
+ pr.proname || '(' || val || ')'
+ ELSE
+ pr.proname::text
+ END
+ FROM name_with_args_tab
+ ) AS name_with_args,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -26,7 +34,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
pr.prokind = 'p'
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/11_plus/properties.sql
index c8d7ae854..6bc8e1835 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/11_plus/properties.sql
@@ -1,16 +1,24 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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::text
- END) AS name_with_args,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (
+ WITH name_with_args_tab AS (SELECT pg_catalog.pg_get_function_identity_arguments(pr.oid) AS val)
+ SELECT CASE WHEN
+ val <> ''
+ THEN
+ pr.proname || '(' || val || ')'
+ ELSE
+ pr.proname::text
+ END
+ FROM name_with_args_tab
+ ) AS name_with_args,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -26,7 +34,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
pr.prokind = 'p'::char
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/default/properties.sql
index de6c73541..8dbf5031c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/procedures/ppas/sql/default/properties.sql
@@ -1,16 +1,23 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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::text
- END) AS name_with_args,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
+ (
+ WITH name_with_args_tab AS (SELECT pg_catalog.pg_get_function_identity_arguments(pr.oid) AS val)
+ SELECT CASE WHEN
+ val <> ''
+ THEN
+ pr.proname || '(' || val || ')'
+ ELSE
+ pr.proname::text
+ END
+ FROM name_with_args_tab
+ ) AS name_with_args,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -26,7 +33,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
AND typname NOT IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/11_plus/properties.sql
index c96beb616..9c64a486a 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/11_plus/properties.sql
@@ -1,11 +1,13 @@
SELECT
pr.oid, pr.xmin,
CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
- pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -21,7 +23,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
pr.prokind IN ('f', 'w')
AND typname IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/9.2_plus/properties.sql
index 6e09dd9de..c06915ada 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/9.2_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/9.2_plus/properties.sql
@@ -1,6 +1,9 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
@@ -19,7 +22,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
AND typname IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/default/properties.sql
index 8e56bea5f..c06915ada 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/pg/sql/default/properties.sql
@@ -1,6 +1,9 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
@@ -19,10 +22,11 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
- AND typname = 'trigger' AND lanname != 'edbspl'
+ AND typname IN ('trigger', 'event_trigger')
+ AND lanname NOT IN ('edbspl', 'sql', 'internal')
{% if fnid %}
AND pr.oid = {{fnid}}::oid
{% else %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/11_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/11_plus/properties.sql
index 38d7445e2..d29507910 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/11_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/11_plus/properties.sql
@@ -1,11 +1,13 @@
SELECT
pr.oid, pr.xmin,
CASE WHEN pr.prokind = 'w' THEN true ELSE false END AS proiswindow,
- pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.prosrc, pr.prosrc AS prosrc_c, pr.pronamespace, pr.prolang, pr.procost, pr.prorows, pr.prokind,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile, pr.proparallel,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
+ pr.pronargdefaults, proconfig, pg_get_userbyid(proowner) AS funcowner, description,
(SELECT
array_agg(provider || '=' || label)
FROM
@@ -21,7 +23,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
pr.prokind IN ('f', 'w')
AND typname IN ('trigger', 'event_trigger') AND lanname != 'edbspl'
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/9.2_plus/properties.sql
index 6e09dd9de..c06915ada 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/9.2_plus/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/9.2_plus/properties.sql
@@ -1,6 +1,9 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
@@ -19,7 +22,7 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
AND typname IN ('trigger', 'event_trigger')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/default/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/default/properties.sql
index 8e56bea5f..c06915ada 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/default/properties.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/trigger_functions/ppas/sql/default/properties.sql
@@ -1,6 +1,9 @@
SELECT
- pr.oid, pr.xmin, pr.*, pr.prosrc AS prosrc_c,
- pr.proname AS name, pg_get_function_result(pr.oid) AS prorettypename,
+ pr.oid, pr.xmin, pr.proiswindow, pr.prosrc, pr.prosrc AS prosrc_c,
+ pr.pronamespace, pr.prolang, pr.procost, pr.prorows,
+ pr.prosecdef, pr.proleakproof, pr.proisstrict, pr.proretset, pr.provolatile,
+ pr.pronargs, pr.prorettype, pr.proallargtypes, pr.proargmodes, pr.probin, pr.proacl,
+ pr.proname, 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,
@@ -19,10 +22,11 @@ JOIN
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)
+ pg_description des ON (des.objoid=pr.oid AND des.classoid='pg_proc'::regclass and des.objsubid = 0)
WHERE
proisagg = FALSE
- AND typname = 'trigger' AND lanname != 'edbspl'
+ AND typname IN ('trigger', 'event_trigger')
+ AND lanname NOT IN ('edbspl', 'sql', 'internal')
{% if fnid %}
AND pr.oid = {{fnid}}::oid
{% else %}
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: [pgAdmin][RM5024] Refactoring SQL of Functions and Procedures.
In-Reply-To: <CAM9w-_ndX+nnVocX=OqrvgBWpu7vqCNzd+ODYCGN99U569QCqQ@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