Re: [GENERAL] How to retrieve table definition in SQL
It seems to work in version 6.5.2 but not in version 6.5.3 what's happened with -E parameter ? from man psql: ... PSQL(UNIX) PostgreSQL PSQL(UNIX) -E Echo the actual query generated by \d and other backslash commands -f filename Use the file filename as the source of queries instead of reading queries interactively. ... anyway try this one: (replace MY_TABLE with your table name): SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'MY_TABLE' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY attnum ; attnum|attname |typname|attlen|atttypmod|attnotnull|atthasdef --+--+---+--+-+--+- 1|istat |bpchar | -1| 10|t |f 2|nome |bpchar | -1| 54|t |f 3|provincia |bpchar | -1| 6|f |f 4|codice_fiscale|bpchar | -1| 8|f |f 5|cap |bpchar | -1| 9|f |f 6|regione |bpchar | -1| 7|f |f 7|distretto |bpchar | -1| 8|f |f (7 rows) Jeff wrote: That doesn't seem to work for me the -E switch doesn't exist, but the -e (echo) does, which I assume you are referring to. The problem is that postgress doesn't echo anything for \commands only real SQL commands. If it works on yours could you copy the echoed query and forward it to me. Jeff Seese > From: Jose Soares [EMAIL PROTECTED]> > Organization: Sfera Carta > Newsgroups: muc.lists.postgres.questions > Date: 20 Mar 2000 17:04:13 +0100 > Subject: Re: [GENERAL] How to retrieve table definition in SQL > > If you start pgsql with -E parameter > when you type \d tablename> pgsql shows you the query it executes to display > the table definition. > > > Stan Jacobs wrote: > >> Hi everyone, >> >> This probably isn't a Postgres-specific question, but I'm hoping that >> someone knows the answer to this off the top of their heads... :-) >> >> I'd like to retrieve the table definition via SQL. I'm using ColdFusion >> to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table >> info, field names/types/sizes, so that my Coldfusion page/script can >> dynamically build the html forms to edit the tables. >> >> Any ideas how to get to this in SQL? I have another C++ class which >> builds nice table headers with this info, but ColdFusion doesn't seem to >> do that with the returned data. >> >> Thanks! >> >> - Stan - > > -- > Jose' Soares > Bologna, Italy [EMAIL PROTECTED] > > > -- Jose' Soares Bologna, Italy [EMAIL PROTECTED]
Re: [GENERAL] How to retrieve table definition in SQL
Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] How to retrieve table definition in SQL
This isn't quite what I'm looking for, though I can't run a script on that machine, so I need to retrieve it with an SQL query. Ie. "SELECT * from data_key_table_name_something"... *smile* Judging from the few responses so far, it doesn't sound like there's an easy way to do this. On Sun, 19 Mar 2000, omid omoomi wrote: Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] How to retrieve table definition in SQL
Stan Jacobs wrote: This isn't quite what I'm looking for, though I can't run a script on that machine, so I need to retrieve it with an SQL query. Ie. "SELECT * from data_key_table_name_something"... *smile* Judging from the few responses so far, it doesn't sound like there's an easy way to do this. You only need to run the script on the machine to get the initial sql queries to which Omid referred. You can then take those and run them from any client. Psql just shows you an example of how it does what you're trying to do. Not sure how easy it is to get everything right, but psql and pgaccess both do what you seem to be trying to do. Here's a trimmed example from 7.0beta: % psql -d emsdb -E emsdb=# create table foo (id serial, t timestamp); CREATE emsdb=# \d foo * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='foo' * * 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 = 'foo' AND a.attnum 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum * ... Regards, Ed Loehr On Sun, 19 Mar 2000, omid omoomi wrote: Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
