perl DBI/postgres: alternatives to slash commands?

[zhili...@gmail.com]

There was a thread on similar topic
http://groups.google.com/group/comp.databases.postgresql.novice/browse_thread/thread/18b7fab2c5961432/92b69e856087b780?q=slash&rnum=9#92b69e856087b780
but I didn't find answer to the problem --

The Postgres slash commands are not supported by perl DBI (like to show
databases, etc.). Are there standard SQL in Postgres to perform the
same jobs for those slash commands? (like in Oracle "select table_name
from user_tables" for "\dt", etc.).

Thanks in advance.

Joe

[Gurjeet Singh]

It has already been mentioned in the reffrenced thread!!! the '\' commands are speacial psql commands; they are not sent to the server. Instead, these special commands are converted into standard SQL and then sent to the server. If you wish to see these 'hidden' SQL queries, you should invoke psql with the -E switch. Following is a sample run of psql with -E switch. I fired '\d t1' command, which describes the table t1.

sh-2.04$ psql -E test postgres
Welcome to psql 8.2devel, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Warning: Console code page (437) differs from Windows code page (1252)
         8-bit characters may not work correctly. See psql reference
         page "Notes for Windows users" for details.

test=# \d t1
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^t1$'
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '82813'
*********************** ***

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_ex pr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '82813' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, pg_catalog.p
g_get_indexdef(i.i ndexrelid, 0, true), c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = '82813' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
******************** ******

********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '82813' AND (not tgisconstraint  OR NOT EXISTS  (SELECT 1 FROM
pg_catalog.pg_depend d    JOIN pg_catalog.pg_constraint c ON ( d.refclassid = c.
tableoid AND d.refobjid = c.oid)    WHERE d.classid = t.tableoid AND d.objid = t
.oid AND d.deptype = 'i' AND c.contype = 'f'))   ORDER BY 1
**************************

********* QUERY **********
SELECT conname,
  pg_catalog.pg_get_constraintdef(oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '82813' AND r.contype = 'f' ORDER BY 1
**************************

*** ****** QUERY **********
SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oi
d=i.inhparent AND i.inhrelid = '82813' ORDER BY inhseqno ASC
**************************

      Table " public.t1"
Column |  Type   | Modifiers
--------+---------+-----------
a      | integer | not null
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a)

test=#