Thread: MySQLs Describe emulator!
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
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
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 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: > 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 >
* 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 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 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
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