Thread: Porting from MySql - meta data issues
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
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