Thread: psql \dp equivalent or similar query?
I couldn't find it on the net. I also coudn't find any reference to it in the psql source? Anyone any suggestions? Regards, Davor
In response to Davor J. : > I couldn't find it on the net. I also coudn't find any reference to it in > the psql source? > > Anyone any suggestions? Start your psql with option -E to display the query behind: kretschmer@tux:~$ psql -E test psql (8.4.2) Type "help" for help. test=# \dp foo ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as "Type", pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges", pg_catalog.array_to_string(ARRAY( SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ') FROM pg_catalog.pg_attribute a WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL ), E'\n') AS "Column access privileges" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v', 'S') AND c.relname ~ '^(foo)$' AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1, 2; ************************** Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+----------+-------------------+-------------------------- public | foo | sequence | | (1 row) test=# Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
Great option! Thanks Andreas ""A. Kretschmer"" <andreas.kretschmer@schollglas.com> wrote in message news:20100706093242.GD13709@a-kretschmer.de... > In response to Davor J. : >> I couldn't find it on the net. I also coudn't find any reference to it in >> the psql source? >> >> Anyone any suggestions? > > Start your psql with option -E to display the query behind: > > kretschmer@tux:~$ psql -E test > psql (8.4.2) > Type "help" for help. > > test=# \dp foo > ********* QUERY ********** > SELECT n.nspname as "Schema", > c.relname as "Name", > CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN > 'sequence' END as "Type", > pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges", > pg_catalog.array_to_string(ARRAY( > SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n > ') > FROM pg_catalog.pg_attribute a > WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL > ), E'\n') AS "Column access privileges" > FROM pg_catalog.pg_class c > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace > WHERE c.relkind IN ('r', 'v', 'S') > AND c.relname ~ '^(foo)$' > AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid) > ORDER BY 1, 2; > ************************** > > Access privileges > Schema | Name | Type | Access privileges | Column access privileges > --------+------+----------+-------------------+-------------------------- > public | foo | sequence | | > (1 row) > > test=# > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >