Обсуждение: VERY basic psql and schema question

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?

Thanks,
Sean

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

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