Thread: MySQLs Describe emulator!

MySQLs Describe emulator!

From

Boulat Khakimov

Date:

06 March 2001, 00:19:00

Hi,

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.

SELECT a.attname AS Field,
       c.typname as Type,
       a.atttypmod-4 AS Size

FROM pg_attribute a,
     pg_class b,
     pg_type c

WHERE a.attrelid=b.oid
      AND a.attname='[fieldname]'
      AND b.relname='[tablename]'
      AND c.OID=a.atttypid;

Output looks like this
funio=# SELECT a.attname AS Field, c.typname as Type,
funio-# a.atttypmod-4 AS Size
funio-# FROM pg_attribute a, pg_class b, pg_type c
funio-# WHERE a.attrelid=b.oid
funio-#       AND a.attname='company'
funio-#       AND b.relname='tbluser'
funio-#       AND c.OID=a.atttypid;

  field  |  type   | size
---------+---------+------
 company | varchar |   50
(1 row)

Pretty nifty huh? ;)

If I have time im gonna make a buildin function (DESC) out of it

--
Nothing Like the Sun

Re: MySQLs Describe emulator!

From

Tom Lane

Date:

06 March 2001, 00:51:54

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

Re: MySQLs Describe emulator!

From

"Gregory Wood"

Date:

06 March 2001, 02:48:07

> 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

Re: MySQLs Describe emulator!

From

Boulat Khakimov

Date:

06 March 2001, 12:13:38

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

Re: MySQLs Describe emulator!

From

Karel Zak

Date:

06 March 2001, 12:55:45

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

Re: Re: MySQLs Describe emulator!

From

Boulat Khakimov

Date:

06 March 2001, 13:18:27

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

Re: Re: MySQLs Describe emulator!

From

Karel Zak

Date:

06 March 2001, 13:54:26

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

Re: Re: MySQLs Describe emulator!

From

Michelle Murrain

Date:

06 March 2001, 13:57:35

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

Re: Re: MySQLs Describe emulator!

From

Alfred Perlstein

Date:

06 March 2001, 14:00:01

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

Re: [SQL] Re: MySQLs Describe emulator!

From

Michael Fork

Date:

06 March 2001, 14:10:17

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:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: [SQL] MySQLs Describe emulator!

From

clayton cottingham

Date:

12 March 2001, 18:15:14

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

Re: [SQL] Re: Re: MySQLs Describe emulator!

From

Mathijs Brands

Date:

12 March 2001, 18:32:15

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

Re: [SQL] Re: MySQLs Describe emulator!

From

Patrick Welche

Date:

13 March 2001, 00:13:00

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