Skip site navigation (1) Skip section navigation (2)

Re: Discussion - Search Objects

From: Dave Page <dpage(at)pgadmin(dot)org>
To: Guillaume Lelarge <guillaume(at)lelarge(dot)info>
Cc: Jasmin Dizdarevic <jasmin(dot)dizdarevic(at)gmail(dot)com>, pgadmin-hackers(at)postgresql(dot)org
Subject: Re: Discussion - Search Objects
Date: 2011-06-01 07:50:28
Message-ID: BANLkTinEJtV49XXR3L5G1pU1ZsNVx7X6Bw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
On Wed, Jun 1, 2011 at 6:06 AM, Guillaume Lelarge
<guillaume(at)lelarge(dot)info> wrote:
> On Wed, 2011-06-01 at 01:05 +0200, Jasmin Dizdarevic wrote:
>> To better get into the code I've decided to implement a feature from
>> the todo list: "Ability to search a database for objects with a
>> specific name"
>> Now I have a first result, which I like to discuss with you.
>>
>>
>> 1. The search is done with this query
>> select * from (
>> select 'TABLE' as type, table_name as objectname, table_schema as path
>> from information_schema.views
>> union
>> select 'VIEW' as type, table_name, table_schema from
>> information_schema.views
>> union
>> select 'COLUMN', column_name, table_schema ||'.'||table_name from
>> information_schema.columns
>> union
>> select 'TRIGGER', trigger_name, event_object_schema||'.'||
>> event_object_table from information_schema.triggers
>> union
>> select 'FUNCTION', routine_name, specific_schema from
>> information_schema.routines
>> union
>> select 'SCHEMA', schema_name, '' from information_schema.schemata
>> union
>> select 'SEQUENCE', sequence_name, sequence_schema from
>> information_schema.sequences
>> union
>> select 'FSERVER', foreign_server_name, '' from
>> information_schema.foreign_servers
>> union
>> select 'FDW', foreign_data_wrapper_name, '' from
>> information_schema.foreign_data_wrappers
>> ) i
>> where lower(objectname) like '%PATTERN%'
>> order by 1,2
>>
>
> You miss quite a lot of objects.

Further, we have a general policy of not using information_schema in
pgAdmin, as users may drop it if they don't need it.

Sorry :-(

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgadmin-hackers by date

Next:From: Guillaume LelargeDate: 2011-06-01 07:53:46
Subject: Re: Discussion - Search Objects
Previous:From: Nikhil SDate: 2011-06-01 06:38:02
Subject: Re: pgAdmin III - Default server info from postgres-reg.ini not getting picked up

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group