public inbox for [email protected]
help / color / mirror / Atom feed[pgAdmin][RM5024] Refactoring SQL of Functions and Procedures.
2+ messages / 2 participants
[nested] [flat]
* [pgAdmin][RM5024] Refactoring SQL of Functions and Procedures.
@ 2019-12-24 07:26 Aditya Toshniwal <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Aditya Toshniwal @ 2019-12-24 07:26 UTC (permalink / raw)
To: pgadmin-hackers
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 %}
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [pgAdmin][RM5024] Refactoring SQL of Functions and Procedures.
@ 2019-12-31 07:55 Akshay Joshi <[email protected]>
parent: Aditya Toshniwal <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Akshay Joshi @ 2019-12-31 07:55 UTC (permalink / raw)
To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers
Thanks, patch applied.
On Tue, Dec 24, 2019 at 12:57 PM Aditya Toshniwal <
[email protected]> wrote:
> 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"
>
--
*Thanks & Regards*
*Akshay Joshi*
*Sr. Software Architect*
*EnterpriseDB Software India Private Limited*
*Mobile: +91 976-788-8246*
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-12-31 07:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-24 07:26 [pgAdmin][RM5024] Refactoring SQL of Functions and Procedures. Aditya Toshniwal <[email protected]>
2019-12-31 07:55 ` Akshay Joshi <[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