Re: VERY basic psql and schema question

Perfect. And I get to learn something, as a bonus!

Thanks,
Sean

On Mar 9, 2005, at 1:39 PM, John DeSoi wrote:

>
> On Mar 9, 2005, at 12:41 PM, Sean Davis wrote:
>
>> If I want to use psql to show me all tables owned by me in any schema
>> of the current database, how can I do that--that is, is there a
>> "shortcut" in psql, or just querying the system catalog?
>>
>
> I don't think there is a way to filter just the ones you own. But if
> you execute he commands below you can see what psql is doing for the
> dt command and make a small modification to the query to get only the
> ones you own.
>
> \set ECHO_HIDDEN 1
> \dt *.*
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
>
>
> \set ECHO_HIDDEN 1
> \dt *.*
> ********* QUERY **********
> SELECT n.nspname as "Schema",
> c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
> THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
> "Type",
> u.usename as "Owner"
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
> AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
> ORDER BY 1,2;
> **************************
>
> List of relations
> Schema | Name | Type | Owner
> --------------------+-------------------------+-------+-------
> information_schema | sql_features | table | desoi
> information_schema | sql_implementation_info | table | desoi
> information_schema | sql_languages | table | desoi
> information_schema | sql_packages | table | desoi
> information_schema | sql_sizing | table | desoi
> information_schema | sql_sizing_profiles | table | desoi
> public | found_test_tbl | table | user1
> public | hslot | table | user1
> ...