Re: selecting tables and columns

On Mon, 2004-06-07 at 18:36, Robert Morgan wrote:
> Hi does postgresql have a similar command to SHOW in MySQL.
> I want to select all tables that match a criteria then select all columns.
> from those tables.
>
> in MySQL:  "SHOW tables like 'stu%'";
>                    "SHOW columns from ".$tbl." ";
> using php variable

The problem here is that in PostgreSQL the presentation layer is complex
enough that it was incorporated into the psql monitor program
originally, via a series of commands implemented with back slash, like
\d to show all tables, and \d tablename to show info on table tablename.

Now, as of 7.4 or so, PostgreSQL implements the INFORMATION_SCHEMA as
defined by the SQL Spec.

A good way to explore the information schema is to do the following from
a psql session:

psql mydatabase
mydatabase=> set search_path TO INFORMATION_SCHEMA, public;
mydatabase=> \d
                             List of relations
       Schema       |              Name               |   Type   |
Owner
--------------------+---------------------------------+----------+----------
 information_schema | applicable_roles                | view     |
postgres
 information_schema | check_constraints               | view     |
postgres
 information_schema | column_domain_usage             | view     |
postgres
 information_schema | column_privileges               | view     |
postgres
 information_schema | column_udt_usage                | view     |
postgres

and so on from there.  You can select * from information_schema.name
with any of the tables shown here to get information from the
information_schema.  The good news is that as other databases implement
the information_schema part of the SQL spec, this will be more and more
portable.