One more patch for the Search Objects dialog.
It allows to search by materialized views: names, definition and
comments (relevant for the
postgres' latest versions). Could you please check and apply it?
Thank you.
--
Dmitriy Olshevskiy
>From a6eb1d1bdb08bb90896a07c00fb2f2f67c476201 Mon Sep 17 00:00:00 2001
From: olshevskiy87 <[email protected]>
Date: Sun, 8 Mar 2015 16:38:04 +0400
Subject: [PATCH] search by materialized views in "search objects" dialog
---
pgadmin/dlg/dlgSearchObject.cpp | 34 +++++++++++++++++-----------------
1 file changed, 17 insertions(+), 17 deletions(-)
diff --git a/pgadmin/dlg/dlgSearchObject.cpp b/pgadmin/dlg/dlgSearchObject.cpp
index 3ea05f5..a933ac3 100644
--- a/pgadmin/dlg/dlgSearchObject.cpp
+++ b/pgadmin/dlg/dlgSearchObject.cpp
@@ -361,19 +361,19 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT(" CASE ")
wxT(" WHEN c.relkind = 'r' THEN 'Tables' ")
wxT(" WHEN c.relkind = 'S' THEN 'Sequences' ")
- wxT(" WHEN c.relkind = 'v' THEN 'Views' ")
+ wxT(" WHEN c.relkind IN ('v','m') THEN 'Views' ")
wxT(" ELSE 'should not happen' ")
wxT(" END AS type, c.relname AS objectname, ")
wxT(" ':Schemas/' || n.nspname || '/' || ")
wxT(" CASE ")
wxT(" WHEN c.relkind = 'r' THEN ':Tables' ")
wxT(" WHEN c.relkind = 'S' THEN ':Sequences' ")
- wxT(" WHEN c.relkind = 'v' THEN ':Views' ")
+ wxT(" WHEN c.relkind IN ('v','m') THEN ':Views' ")
wxT(" ELSE 'should not happen' ")
wxT(" END || '/' || c.relname AS path, n.nspname ")
wxT(" FROM pg_class c ")
wxT(" LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ")
- wxT(" WHERE c.relkind in ('r','S','v') ")
+ wxT(" WHERE c.relkind in ('r','S','v','m') ")
wxT(" UNION ")
wxT(" SELECT 'Indexes', cls.relname, ':Schemas/' || n.nspname || '/:Tables/' || tab.relname || '/:Indexes/' || cls.relname, n.nspname ")
wxT(" FROM pg_index idx ")
@@ -399,11 +399,11 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT(" case ")
wxT(" when t.relkind = 'r' then ':Tables' ")
wxT(" when t.relkind = 'S' then ':Sequences' ")
- wxT(" when t.relkind = 'v' then ':Views' ")
+ wxT(" when t.relkind in ('v','m') then ':Views' ")
wxT(" else 'should not happen' ")
wxT(" end || '/' || t.relname || '/:Columns/' || a.attname AS path, n.nspname ")
wxT(" from pg_attribute a ")
- wxT(" inner join pg_class t on a.attrelid = t.oid and t.relkind in ('r','v') ")
+ wxT(" inner join pg_class t on a.attrelid = t.oid and t.relkind in ('r','v','m') ")
wxT(" left join pg_namespace n on t.relnamespace = n.oid where a.attnum > 0 ")
wxT(" union ")
wxT(" select 'Constraints', case when tf.relname is null then c.conname else c.conname || ' -> ' || tf.relname end, ':Schemas/' || n.nspname||'/:Tables/'||t.relname||'/:Constraints/'||case when tf.relname is null then c.conname else c.conname || ' -> ' || tf.relname end, n.nspname from pg_constraint c ")
@@ -411,11 +411,11 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT(" left join pg_class tf on c.confrelid = tf.oid ")
wxT(" left join pg_namespace n on t.relnamespace = n.oid ")
wxT(" union ")
- wxT(" select 'Rules', r.rulename, ':Schemas/' || n.nspname||case when t.relkind = 'v' then '/:Views/' else '/:Tables/' end||t.relname||'/:Rules/'|| r.rulename, n.nspname from pg_rewrite r ")
+ wxT(" select 'Rules', r.rulename, ':Schemas/' || n.nspname||case when t.relkind in ('v','m') then '/:Views/' else '/:Tables/' end||t.relname||'/:Rules/'|| r.rulename, n.nspname from pg_rewrite r ")
wxT(" left join pg_class t on r.ev_class = t.oid ")
wxT(" left join pg_namespace n on t.relnamespace = n.oid ")
wxT(" union ")
- wxT(" select 'Triggers', tr.tgname, ':Schemas/' || n.nspname||case when t.relkind = 'v' then '/:Views/' else '/:Tables/' end||t.relname || '/:Triggers/' || tr.tgname, n.nspname from pg_trigger tr ")
+ wxT(" select 'Triggers', tr.tgname, ':Schemas/' || n.nspname||case when t.relkind in ('v','m') then '/:Views/' else '/:Tables/' end||t.relname || '/:Triggers/' || tr.tgname, n.nspname from pg_trigger tr ")
wxT(" left join pg_class t on tr.tgrelid = t.oid ")
wxT(" left join pg_namespace n on t.relnamespace = n.oid ")
wxT(" where ");
@@ -542,13 +542,13 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT("case ")
wxT(" when t.relkind = 'r' then ':Tables' ")
wxT(" when t.relkind = 'S' then ':Sequences' ")
- wxT(" when t.relkind = 'v' then ':Views' ")
+ wxT(" when t.relkind in ('v','m') then ':Views' ")
wxT(" else 'should not happen' ")
wxT("end || '/' || t.relname || '/:Columns/' || a.attname AS path, n.nspname ")
wxT("from pg_attribute a ")
wxT("inner join pg_type ty on a.atttypid = ty.oid ")
wxT("left join pg_attrdef ad on a.attrelid = ad.adrelid and a.attnum = ad.adnum ")
- wxT("inner join pg_class t on a.attrelid = t.oid and t.relkind in ('r','v') ")
+ wxT("inner join pg_class t on a.attrelid = t.oid and t.relkind in ('r','v','m') ")
wxT("left join pg_namespace n on t.relnamespace = n.oid ")
wxT("where a.attnum > 0 ")
wxT(" and (ty.typname ilike ") + txtPatternStr + wxT(" or ad.adsrc ilike ") + txtPatternStr + wxT(") ")
@@ -557,7 +557,7 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT("':Schemas/' || n.nspname || '/:Views/' || c.relname, n.nspname ")
wxT(" FROM pg_class c ")
wxT(" LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ")
- wxT(" WHERE c.relkind = 'v' ")
+ wxT(" WHERE c.relkind IN ('v','m') ")
wxT(" and pg_get_viewdef(c.oid) ilike ") + txtPatternStr + wxT(" ")
wxT("UNION ") // Relation's column names except for Views (searched earlier)
wxT("SELECT CASE ")
@@ -609,20 +609,20 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
searchSQL += wxT("SELECT CASE")
wxT(" WHEN c.relkind = 'r' THEN 'Tables'")
wxT(" WHEN c.relkind = 'S' THEN 'Sequences'")
- wxT(" WHEN c.relkind = 'v' THEN 'Views'")
+ wxT(" WHEN c.relkind IN ('v','m') THEN 'Views'")
wxT(" ELSE 'should not happen'")
wxT(" END AS type, c.relname AS objectname,")
wxT(" ':Schemas/' || n.nspname || '/' ||")
wxT(" CASE")
wxT(" WHEN c.relkind = 'r' THEN ':Tables'")
wxT(" WHEN c.relkind = 'S' THEN ':Sequences'")
- wxT(" WHEN c.relkind = 'v' THEN ':Views'")
+ wxT(" WHEN c.relkind IN ('v','m') THEN ':Views'")
wxT(" ELSE 'should not happen'")
wxT(" END || '/' || c.relname AS path, n.nspname")
wxT(" FROM ") + pd +
wxT(" JOIN pg_class c on pd.relname = 'pg_class' and pd.objoid = c.oid")
wxT(" LEFT JOIN pg_namespace n ON n.oid = c.relnamespace")
- wxT(" WHERE c.relkind in ('r','S','v')")
+ wxT(" WHERE c.relkind in ('r','S','v','m')")
wxT(" UNION")
wxT(" SELECT 'Indexes', cls.relname, ':Schemas/' || n.nspname || '/:Tables/' || tab.relname || '/:Indexes/' || cls.relname, n.nspname")
wxT(" FROM ") + pd +
@@ -654,11 +654,11 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT(" case")
wxT(" when t.relkind = 'r' then ':Tables'")
wxT(" when t.relkind = 'S' then ':Sequences'")
- wxT(" when t.relkind = 'v' then ':Views'")
+ wxT(" when t.relkind in ('v','m') then ':Views'")
wxT(" else 'should not happen'")
wxT(" end || '/' || t.relname || '/:Columns/' || a.attname AS path, n.nspname")
wxT(" from ") + pd +
- wxT(" join pg_class t on pd.relname = 'pg_class' and pd.objoid = t.oid and t.relkind in ('r','v')")
+ wxT(" join pg_class t on pd.relname = 'pg_class' and pd.objoid = t.oid and t.relkind in ('r','v','m')")
wxT(" join pg_attribute a on a.attrelid = t.oid and pd.objsubid = a.attnum")
wxT(" left join pg_namespace n on t.relnamespace = n.oid where a.attnum > 0")
wxT(" union")
@@ -672,13 +672,13 @@ void dlgSearchObject::OnSearch(wxCommandEvent &ev)
wxT(" left join pg_class tf on c.confrelid = tf.oid")
wxT(" left join pg_namespace n on t.relnamespace = n.oid")
wxT(" union")
- wxT(" select 'Rules', r.rulename, ':Schemas/' || n.nspname||case when t.relkind = 'v' then '/:Views/' else '/:Tables/' end||t.relname||'/:Rules/'|| r.rulename, n.nspname")
+ wxT(" select 'Rules', r.rulename, ':Schemas/' || n.nspname||case when t.relkind in ('v','m') then '/:Views/' else '/:Tables/' end||t.relname||'/:Rules/'|| r.rulename, n.nspname")
wxT(" from ") + pd +
wxT(" join pg_rewrite r on pd.relname = 'pg_rewrite' and pd.objoid = r.oid")
wxT(" left join pg_class t on r.ev_class = t.oid")
wxT(" left join pg_namespace n on t.relnamespace = n.oid")
wxT(" union")
- wxT(" select 'Triggers', tr.tgname, ':Schemas/' || n.nspname||case when t.relkind = 'v' then '/:Views/' else '/:Tables/' end||t.relname || '/:Triggers/' || tr.tgname, n.nspname")
+ wxT(" select 'Triggers', tr.tgname, ':Schemas/' || n.nspname||case when t.relkind in ('v','m') then '/:Views/' else '/:Tables/' end||t.relname || '/:Triggers/' || tr.tgname, n.nspname")
wxT(" from ") + pd +
wxT(" join pg_trigger tr on pd.relname = 'pg_trigger' and pd.objoid = tr.oid")
wxT(" left join pg_class t on tr.tgrelid = t.oid")
--
1.8.3.msysgit.0
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
