Re: Porting from MySql - meta data issues

От:

"Gregory Wood" <gregw@com-stock.com>

Дата:

8 апреля 2002 г. в 11:52:12

> > 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

Re: Porting from MySql - meta data issues

От:

Jeff Eckermann <jeff_eckermann@yahoo.com>

Дата:

5 апреля 2002 г. в 10:44:52

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  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/

Re: Porting from MySql - meta data issues

От:

Alvaro Herrera <alvherre@atentus.com>

Дата:

6 апреля 2002 г. в 23:24:28

En Fri, 05 Apr 2002 17:13:40 +0100
Geoff Caplan  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 ()
"Everybody understands Mickey Mouse. Few understand Hermann Hesse.
Hardly anybody understands Einstein. And nobody understands Emperor Norton."

Re: Porting from MySql - meta data issues

От:

Jean-Michel POURE <jm.poure@freesurf.fr>

Дата:

9 апреля 2002 г. в 02:33:02

> 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

Re: Porting from MySql - meta data issues

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

7 апреля 2002 г. в 00:49:19

Alvaro Herrera  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

Re: Porting from MySql - meta data issues

От:

Andreas Scherbaum <adsmail@htl.de>

Дата:

5 апреля 2002 г. в 08:40:50

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

Re: Porting from MySql - meta data issues

От:

Joe Conway <mail@joeconway.com>

Дата:

5 апреля 2002 г. в 10:03:01

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

Porting from MySql - meta data issues

От:

Geoff Caplan <geoff@advantae.com>

Дата:

5 апреля 2002 г. в 08:33:32

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

Re: Porting from MySql - meta data issues

От:

Geoff Caplan <geoff@advantae.com>

Дата:

5 апреля 2002 г. в 15:49:10



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