MySQLs Describe emulator!

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

regards, tom lane

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

Those without shell access (or those of us who have access but use tools
other than psql) would like in on the fun too :)

Greg

Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

Hi,

1) "\d table" can only be used in psql, you cant run a query like that
using libpq for example

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

Regards,
Boulat Khakimov

--
Nothing Like the Sun

On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote:

Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Karel Zak wrote:

On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote:
Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.

Karel

Agreed! Why make someones life easier??
Let's complicate things as much as possible that way it's more
fun,right? ;o)

Dont understand how this works? No problem -- just read the source
code.
Dont understand how to get that to work? Not a problem -- read the
source code!

The only problem tho, the source codes tend to be thousands of lines
when it comes
to DBs and time is ...

Regards,
Boulat Khakimov

--
Nothing Like the Sun

On Tuesday 06 March 2001 10:19 am, Boulat Khakimov wrote:

Karel Zak wrote:

On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote:

Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.

Karel

Agreed! Why make someones life easier??
Let's complicate things as much as possible that way it's more
fun,right? ;o)

Dont understand how this works? No problem -- just read the source
code.
Dont understand how to get that to work? Not a problem -- read the
source code!

The only problem tho, the source codes tend to be thousands of lines
when it comes
to DBs and time is ...

And, further, some of us are web programmers, and the source code doesn't
help one whit in getting DATA that one needs to do stuff on the front end,
like data entry validation.

Thanks much Boulat - you made the coding for my robust validator a lot easier
to write and read... and it works well, too!

Michelle
------------
Michelle Murrain, Ph.D.
President
Norwottuck Technology Resources
mpm@norwottuck.com
http://www.norwottuck.com

* Boulat Khakimov <boulat@inet-interactif.com> [010306 07:24] wrote:

Karel Zak wrote:

On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote:
Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.

FreeBSD has had some great successes because we're able to emulate
Linux, perhaps something in contrib or even the base system could
offer a MySQL compatibility module to help people ease into Postgresql
from Mysql?

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

On Tue, Mar 06, 2001 at 10:19:13AM -0500, Boulat Khakimov wrote:

Karel Zak wrote:

On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote:
Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.

Karel

Agreed! Why make someones life easier??
Let's complicate things as much as possible that way it's more
fun,right? ;o)

Dont understand how this works? No problem -- just read the source
code.
Dont understand how to get that to work? Not a problem -- read the
source code!

The only problem tho, the source codes tend to be thousands of lines
when it comes
to DBs and time is ...

Well man, I not write this code, but I need 1 minute for found it....

see src/bin/psql/describe.c:

SELECT a.attname, format_type(a.atttypid, a.atttypmod), attnotnull,
a.atthasdef, a.attnum, obj_description(a.oid)
FROM pg_class c, pg_attribute a
WHERE c.relname = 'YourTableName' AND
^^^^^^^^^^^^^
a.attnum > 0 AND
a.attrelid = c.oid
ORDER BY a.attnum;

If I good remenber anywhere in PG's docs is catalog schema. It isn't
too much difficult write queries like above-mentioned, because catalog
attributes/tables names are intuitive. For start see pg_class and
pg_attribute.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

try starting psql with the -E option -- this displays all queries used
internally to the screen, i.e.:

bash-2.04$ psql -E
********* QUERY *********
SELECT usesuper FROM pg_user WHERE usename = 'mfork'
*************************

Welcome to psql, the PostgreSQL interactive terminal.

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

mfork=# \d test
********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='test'
*************************

********* QUERY *********
SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'test'
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
ORDER BY a.attnum
*************************

Table "test"
Attribute | Type | Modifier
-----------+------+----------
t | text |
d | date |

So to get the info displayed with \d, execute the query:

SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '<<TABLE NAME>>'
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
ORDER BY a.attnum

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Tue, 6 Mar 2001, Boulat Khakimov wrote:

heya:

why not try:

drfrog=# \d messages
            Table "messages"
     Attribute     |  Type   | Modifier 
-------------------+---------+----------
 id                | integer | not null
 msgid             | text    | 
 boxid             | integer | 
 accountid         | integer | 
 date              | text    | 
 sentto            | text    | 
 sentfrom          | text    | 
 subject           | text    | 
 contenttype       | text    | 
 contentxferencode | text    | 
 mimeversion       | text    | 
 precedence        | text    | 
 approvedby        | text    | 
 inreplyto         | text    | 
 replyto           | text    | 
 listsub           | text    | 
 listunsub         | text    | 
 status            | text    | 
 xorigip           | text    | 
 cc                | text    | 
 bcc               | text    | 
 sender            | text    | 
 returnpath        | text    | 
 priority          | text    | 
 xmailer           | text    | 
 xuidl             | text    | 
 xsender           | text    | 
 localdate         | text    | 
 newmsg            | text    | 
 replyf            | integer | 
 friendly          | text    | 
 rreceiptto        | text    | 
 score             | integer | 
 ref               | text    | 
 serverstat        | integer | 
Index: messages_pkey

On Tue, Mar 06, 2001 at 04:37:32PM +0100, Karel Zak allegedly wrote:

On Tue, Mar 06, 2001 at 10:19:13AM -0500, Boulat Khakimov wrote:

Karel Zak wrote:

On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote:
Tom Lane wrote:

Boulat Khakimov <boulat@inet-interactif.com> writes:

Here is a nifty query I came up with
that provides a detailed information on any row of any table.
Something that is build into mySQL (DESC tablename fieldname)
but not into PG.

Er, what's wrong with psql's "\d table" ?

2) as a programmer I need to be able to find out as much info as
possible about any given field
which is what "describe" for in mySQL.

As a programmer you can see psql source and directly found how SQL
query execute this tool. The PostgreSQL needn't non-standard statements
like MySQL's SHOW, DESC -- the postgreSQL has system catalogs.

Karel

Agreed! Why make someones life easier??
Let's complicate things as much as possible that way it's more
fun,right? ;o)

Dont understand how this works? No problem -- just read the source
code.
Dont understand how to get that to work? Not a problem -- read the
source code!

The only problem tho, the source codes tend to be thousands of lines
when it comes
to DBs and time is ...

Well man, I not write this code, but I need 1 minute for found it....

see src/bin/psql/describe.c:

SELECT a.attname, format_type(a.atttypid, a.atttypmod), attnotnull,
a.atthasdef, a.attnum, obj_description(a.oid)
FROM pg_class c, pg_attribute a
WHERE c.relname = 'YourTableName' AND
^^^^^^^^^^^^^
a.attnum > 0 AND
a.attrelid = c.oid
ORDER BY a.attnum;

If I good remenber anywhere in PG's docs is catalog schema. It isn't
too much difficult write queries like above-mentioned, because catalog
attributes/tables names are intuitive. For start see pg_class and
pg_attribute.

Karel, how about this one? It's even easier :) No need to spit through code
to find this...

serv0:/var/namedsrc$ psql -E -c '\d nodes' iig
********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='nodes'
*************************

********* QUERY *********
SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'nodes'
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'nodes' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY c2.relname
*************************

Table "nodes"
Attribute | Type | Modifier
-----------+----------+----------
id | integer |
title | text |
ncount | smallint |
ecount | smallint |
ref | integer |
moddate | integer |
publish | char(1) |
Indices: idx_nodes_id,
idx_nodes_ref,
idx_nodes_title

Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum

On Wed, 7 Mar 2001, Mathijs Brands wrote:

If I good remenber anywhere in PG's docs is catalog schema. It isn't
too much difficult write queries like above-mentioned, because catalog
attributes/tables names are intuitive. For start see pg_class and
pg_attribute.

Karel, how about this one? It's even easier :) No need to spit through code
to find this...

serv0:/var/namedsrc$ psql -E -c '\d nodes' iig

Yes, "-E" is better solution and I didn't know it, next time will probably
better see docs and help (psql --hepl) before start discussion like this.

Thanks
Karel

PS. ...but nobody be taken ill with source code :-)

On Tue, Mar 06, 2001 at 10:38:43AM -0500, Michael Fork wrote:

try starting psql with the -E option -- this displays all queries used
internally to the screen, i.e.:

Sorry, hadn't read this one before posting... Thanks to the "moderating" it'll
all be out of synch anyway but..

Patrick