[pgAdmin4][PATCH] To fix the issue in Synonym node EPAS9.2

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Murtuza Zabuawala <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin4][PATCH] To fix the issue in Synonym node EPAS9.2
Date: Mon, 5 Jun 2017 16:08:48 +0530
Message-ID: <CAKKotZSNw-seEckwbHepsoBAkCzkeEZBQ8216ge3PGGNHE5ncA@mail.gmail.com> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgadmin-hackers>

Hi,

PFA patch to fix the issue in Synonym node for EPAS9.2+ where Packages are
not displaying as a  target objects.
RM#1813

--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/get_objects.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/get_objects.sql
new file mode 100644
index 0000000..0ae7aec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/get_objects.sql
@@ -0,0 +1,56 @@
+{###########################################}
+{### If Target Type is Function ###}
+{###########################################}
+{% if trgTyp == 'f' %}
+SELECT DISTINCT proname AS name
+    FROM pg_proc p, pg_namespace n
+WHERE p.pronamespace = n.oid AND
+    n.nspname = {{  trgSchema|qtLiteral }} AND
+    p.protype  = '0'
+ORDER BY proname;
+{###########################################}
+{### If Target Type is Procedure ###}
+{###########################################}
+{% elif trgTyp == 'p' %}
+SELECT DISTINCT proname AS name
+    FROM pg_proc p, pg_namespace n
+WHERE p.pronamespace = n.oid AND
+    n.nspname = {{  trgSchema|qtLiteral }} AND
+    p.protype  = '1'
+ORDER BY proname;
+{###########################################}
+{### If Target Type is Synonym ###}
+{###########################################}
+{% elif trgTyp == 's' %}
+SELECT synname AS name
+    FROM pg_synonym
+ORDER BY synname;
+{###########################################}
+{### If Target Type is Package ###}
+{###########################################}
+{% elif trgTyp == 'P' %}
+SELECT nspname AS name
+    FROM pg_namespace
+WHERE nspparent IN (
+                    SELECT oid
+                        FROM pg_namespace
+                    WHERE nspname = {{ trgSchema|qtLiteral }} LIMIT 1
+                   )
+      AND nspobjecttype = 0
+ORDER BY nspname;
+{% else %}
+{###################################################}
+{### If Target Type is Table/View/M.View/Sequnce ###}
+{###################################################}
+SELECT relname AS name
+    FROM pg_class c, pg_namespace n
+WHERE c.relnamespace = n.oid AND
+    n.nspname = {{  trgSchema|qtLiteral }} AND
+{% if trgTyp == 'v' %}
+{# If view is select then we need to fetch both view and materialized view #}
+ (c.relkind = 'v' OR c.relkind = 'm')
+{% else %}
+    c.relkind = {{ trgTyp|qtLiteral }}
+{% endif %}
+ORDER BY relname;
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..ec2724a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/properties.sql
@@ -0,0 +1,31 @@
+SELECT synname AS name, pg_get_userbyid(synowner) AS owner,
+  synobjschema, synobjname,  ns.nspname as schema,
+  COALESCE(
+  (SELECT relkind
+  FROM pg_class c, pg_namespace n
+  WHERE c.relnamespace = n.oid
+    AND n.nspname = synobjschema
+    AND c.relname = synobjname),
+  -- For Function/Procedure
+  (SELECT CASE WHEN p.protype = '0' THEN 'f'::"char" ELSE 'p'::"char" END
+  FROM pg_proc p, pg_namespace n
+    WHERE p.pronamespace = n.oid
+      AND n.nspname = synobjschema
+      AND p.proname = synobjname LIMIT 1),
+  -- For Package
+  (SELECT CASE WHEN count(*) > 0 THEN 'P'::"char" END
+  FROM pg_namespace
+    WHERE nspparent IN (SELECT oid
+                           FROM pg_namespace
+                        WHERE nspname = synobjschema LIMIT 1)
+      AND nspname = synobjname
+      AND nspobjecttype = 0),
+  -- Default s = Synonym
+  's') AS targettype,
+  CASE WHEN ns.nspname = 'public' THEN true ELSE false END AS is_public_synonym
+FROM pg_synonym s  JOIN pg_namespace ns ON s.synnamespace = ns.oid
+ WHERE s.synnamespace={{scid}}::oid
+ {% if syid %}
+   AND s.synname={{ syid|qtLiteral }}
+ {% endif %}
+ORDER BY synname;
\ No newline at end of file


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


Attachments:

  [text/plain] RM_1813.diff (3.9K, 3-RM_1813.diff)
  download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/get_objects.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/get_objects.sql
new file mode 100644
index 0000000..0ae7aec
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/get_objects.sql
@@ -0,0 +1,56 @@
+{###########################################}
+{### If Target Type is Function ###}
+{###########################################}
+{% if trgTyp == 'f' %}
+SELECT DISTINCT proname AS name
+    FROM pg_proc p, pg_namespace n
+WHERE p.pronamespace = n.oid AND
+    n.nspname = {{  trgSchema|qtLiteral }} AND
+    p.protype  = '0'
+ORDER BY proname;
+{###########################################}
+{### If Target Type is Procedure ###}
+{###########################################}
+{% elif trgTyp == 'p' %}
+SELECT DISTINCT proname AS name
+    FROM pg_proc p, pg_namespace n
+WHERE p.pronamespace = n.oid AND
+    n.nspname = {{  trgSchema|qtLiteral }} AND
+    p.protype  = '1'
+ORDER BY proname;
+{###########################################}
+{### If Target Type is Synonym ###}
+{###########################################}
+{% elif trgTyp == 's' %}
+SELECT synname AS name
+    FROM pg_synonym
+ORDER BY synname;
+{###########################################}
+{### If Target Type is Package ###}
+{###########################################}
+{% elif trgTyp == 'P' %}
+SELECT nspname AS name
+    FROM pg_namespace
+WHERE nspparent IN (
+                    SELECT oid
+                        FROM pg_namespace
+                    WHERE nspname = {{ trgSchema|qtLiteral }} LIMIT 1
+                   )
+      AND nspobjecttype = 0
+ORDER BY nspname;
+{% else %}
+{###################################################}
+{### If Target Type is Table/View/M.View/Sequnce ###}
+{###################################################}
+SELECT relname AS name
+    FROM pg_class c, pg_namespace n
+WHERE c.relnamespace = n.oid AND
+    n.nspname = {{  trgSchema|qtLiteral }} AND
+{% if trgTyp == 'v' %}
+{# If view is select then we need to fetch both view and materialized view #}
+ (c.relkind = 'v' OR c.relkind = 'm')
+{% else %}
+    c.relkind = {{ trgTyp|qtLiteral }}
+{% endif %}
+ORDER BY relname;
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/properties.sql
new file mode 100644
index 0000000..ec2724a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/synonyms/templates/synonym/sql/9.2_plus/properties.sql
@@ -0,0 +1,31 @@
+SELECT synname AS name, pg_get_userbyid(synowner) AS owner,
+  synobjschema, synobjname,  ns.nspname as schema,
+  COALESCE(
+  (SELECT relkind
+  FROM pg_class c, pg_namespace n
+  WHERE c.relnamespace = n.oid
+    AND n.nspname = synobjschema
+    AND c.relname = synobjname),
+  -- For Function/Procedure
+  (SELECT CASE WHEN p.protype = '0' THEN 'f'::"char" ELSE 'p'::"char" END
+  FROM pg_proc p, pg_namespace n
+    WHERE p.pronamespace = n.oid
+      AND n.nspname = synobjschema
+      AND p.proname = synobjname LIMIT 1),
+  -- For Package
+  (SELECT CASE WHEN count(*) > 0 THEN 'P'::"char" END
+  FROM pg_namespace
+    WHERE nspparent IN (SELECT oid
+                           FROM pg_namespace
+                        WHERE nspname = synobjschema LIMIT 1)
+      AND nspname = synobjname
+      AND nspobjecttype = 0),
+  -- Default s = Synonym
+  's') AS targettype,
+  CASE WHEN ns.nspname = 'public' THEN true ELSE false END AS is_public_synonym
+FROM pg_synonym s  JOIN pg_namespace ns ON s.synnamespace = ns.oid
+ WHERE s.synnamespace={{scid}}::oid
+ {% if syid %}
+   AND s.synname={{ syid|qtLiteral }}
+ {% endif %}
+ORDER BY synname;
\ No newline at end of file

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] To fix the issue in Synonym node EPAS9.2
  In-Reply-To: <CAKKotZSNw-seEckwbHepsoBAkCzkeEZBQ8216ge3PGGNHE5ncA@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