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
> ...