Horton
Menu

Thread

  1. MySQLs Describe emulator!

    Boulat Khakimov <boulat@inet-interactif.com> — 2001-03-06T02:16:55Z

    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

  2. Re: MySQLs Describe emulator!

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-06T02:46:17Z

    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

  3. Re: MySQLs Describe emulator!

    Gregory Wood <gregw@com-stock.com> — 2001-03-06T04:48:07Z

    > 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

  4. Re: MySQLs Describe emulator!

    Boulat Khakimov <boulat@inet-interactif.com> — 2001-03-06T14:14:54Z

    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

  5. Re: MySQLs Describe emulator!

    Karel Zak <zakkr@zf.jcu.cz> — 2001-03-06T14:55:22Z

    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

  6. Re: Re: MySQLs Describe emulator!

    Boulat Khakimov <boulat@inet-interactif.com> — 2001-03-06T15:19:13Z

    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

  7. Re: Re: MySQLs Describe emulator!

    Michelle Murrain <mpm@norwottuck.com> — 2001-03-06T15:29:23Z

    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

  8. Re: Re: MySQLs Describe emulator!

    Alfred Perlstein <bright@wintelcom.net> — 2001-03-06T15:29:45Z

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

  9. Re: Re: MySQLs Describe emulator!

    Karel Zak <zakkr@zf.jcu.cz> — 2001-03-06T15:37:32Z

    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

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

    Michael Fork <mfork@toledolink.com> — 2001-03-06T15:38:43Z

    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
>

  11. Re: [SQL] MySQLs Describe emulator!

    clayton <drfrog@smartt.com> — 2001-03-06T16:03:41Z

    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

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

    Mathijs Brands <mathijs@ilse.nl> — 2001-03-07T00:09:46Z

    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

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

    Karel Zak <zakkr@zf.jcu.cz> — 2001-03-07T08:48:49Z

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

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

    Patrick Welche <prlw1@newn.cam.ac.uk> — 2001-03-09T19:43:28Z

    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