Porting from MySql - meta data issues

Geoff Caplan wrote:

I'm porting quite a big PHP application from MySql and have hit an issue.

I have a number of PHP functions in the app which return meta-info about
the data structure:

brave new SQL, once created for database independence... ;-)

With MySql, this info is readily available using the various SHOW queries,
but I can't figure out how to do this with Postgres.

The only option I have come up with is to shell out to psql, run \l and \d
commands, and use a regex to parse the strings returned. Clearly, this
would be a bit of a hack, and not resource efficient.

start psql with the -E option (see the manpage). This will display all
queries,
which the psql tool does send to the backend.
You can fetch all kind of informations you need from the pg_* tables.

Best regards

--
Andreas 'ads' Scherbaum

Geoff Caplan wrote:

Hi folks

I'm porting quite a big PHP application from MySql and have hit an issue.

I have a number of PHP functions in the app which return meta-info about
the data structure:

list_databases( ) ;
list_tables( $database_name ) ;
list_field_names( $table_name ) ;
is_existing_field( $table_name ) ;

With MySql, this info is readily available using the various SHOW
queries, but I can't figure out how to do this with Postgres.

The only option I have come up with is to shell out to psql, run \l and
\d commands, and use a regex to parse the strings returned. Clearly,
this would be a bit of a hack, and not resource efficient.

Can any kind person suggest a more effective approach? I am brand new to
Postgres, and doing this under considerable time pressure, so apologies
if I am missing anything obvious - I can't find anything in the docs, or
in the articles on porting from MySQL...

Start up psql with -E on the command line. That will show you the
internal queries being used by psql to generate the output.

Hope this helps,

Joe

The answers you have already had are good ones.
To add to those: you can find more details about the
system tables here:

http://www5.uk.postgresql.org/users-lounge/docs/7.2/postgres/catalogs.html

--- Geoff Caplan <geoff@advantae.com> wrote:

Hi folks

I'm porting quite a big PHP application from MySql
and have hit an issue.

I have a number of PHP functions in the app which
return meta-info about
the data structure:

list_databases( ) ;
list_tables( $database_name ) ;
list_field_names( $table_name ) ;
is_existing_field( $table_name ) ;

With MySql, this info is readily available using the
various SHOW queries,
but I can't figure out how to do this with Postgres.

The only option I have come up with is to shell out
to psql, run \l and \d
commands, and use a regex to parse the strings
returned. Clearly, this
would be a bit of a hack, and not resource
efficient.

Can any kind person suggest a more effective
approach? I am brand new to
Postgres, and doing this under considerable time
pressure, so apologies if
I am missing anything obvious - I can't find
anything in the docs, or in
the articles on porting from MySQL...

Geoff Caplan
Advantae Ltd

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/

Thanks folks - useful responses.

In the longer run, though, it would be good if there were a more accessible
set of functions or queries (such as the MySql SHOW queries), which made
this kind of meta-data more accessible.

Geoff Caplan

En Fri, 05 Apr 2002 17:13:40 +0100
Geoff Caplan <geoff@advantae.com> escribi�:

Thanks folks - useful responses.

In the longer run, though, it would be good if there were a more accessible
set of functions or queries (such as the MySql SHOW queries), which made
this kind of meta-data more accessible.

You can also use the pg_tables view, for example, and the system
catalogs for the rest (pg_database, pg_attribute).

Anyway, there's little use for SHOW queries, as you can always poke into
the system catalogs. That's something you can't do in MySQL, and is the
reason for the SHOW hack.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."

Alvaro Herrera <alvherre@atentus.com> writes:

You can also use the pg_tables view, for example, and the system
catalogs for the rest (pg_database, pg_attribute).

Anyway, there's little use for SHOW queries, as you can always poke into
the system catalogs. That's something you can't do in MySQL, and is the
reason for the SHOW hack.

If we actually implement anything new in this line, it will presumably
be the SQL-standard-compliant INFORMATION_SCHEMA views. Although as
far as I can see, those have nothing to recommend them except standards
compliance :-(. "SELECT * FROM INFORMATION_SCHEMA.TABLES" doesn't seem
to have any great advantage over either "\d" or whatever the equivalent
MySQL SHOW command is...

regards, tom lane

You can also use the pg_tables view, for example, and the system
catalogs for the rest (pg_database, pg_attribute).

Anyway, there's little use for SHOW queries, as you can always poke into
the system catalogs. That's something you can't do in MySQL, and is the
reason for the SHOW hack.

If we actually implement anything new in this line, it will presumably
be the SQL-standard-compliant INFORMATION_SCHEMA views. Although as
far as I can see, those have nothing to recommend them except standards
compliance :-(. "SELECT * FROM INFORMATION_SCHEMA.TABLES" doesn't seem
to have any great advantage over either "\d" or whatever the equivalent
MySQL SHOW command is...

Except that it's awfully hard to do a "\d" if you're not in psql. To get a
list of tables I had to use "psql -E" to figure out the query so that I
could perform the query myself. The real advantage of a view to me would be
if any changes are made to the system catalog that would 'break' the query,
it would be handled transparently by changing the view.

Greg

I'm porting quite a big PHP application from MySql and have hit an issue.

Maybe you should use pgAdmin2 (http://pgadmin.postgresql.org), PostgreSQL
Windows GUI, which offers several advanced features :

- access to all PostgreSQL objects (tables, views, triggers, rules, functions,
indexes, etc...),
- code editor with syntax highlight (SQL, PLpgSQL, PLperl, PLpython, etc...).
- function modification, pseudo modification of views and triggers,
- data migration wizard.

With pgAdmin2, it should be easy to port your PHP code to PLpgSQL. Maybe you
should start with writing views and add PLpgSQL then.

Cheers,
Jean-Michel