pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
table column information
11+ messages / 6 participants
[nested] [flat]

* table column information
@ 2004-05-16 23:18  Scot L. Harris <webid@cfl.rr.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Scot L. Harris @ 2004-05-16 23:18 UTC (permalink / raw)
  To: pgsql-php

Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat
8.0 system.

I am writing some php scripts where I want to generate a list of the
column names in a particular table that the user selects.  I could take
the brute force method and hard code the column names but then every
time I add a new table or modify an existing one I would have to modify
the code.  What I want is to have a generic function that given the
table name it will pull the column names for my use.

I need to get the table column names for several tables I have setup.  I
know if I do a select * from tablename I can then use the pg_fieldname
function to pull the column names for all columns. 

But I don't think I want to select the entire contents of the table
every time I want to get the names of the columns.  I know this will
work but I think performance will be very poor.  

Trying to find something the equivalent of doing a \d tablename in psql.


I did see a function to pull meta data but that is in a 4.3 version of
php.  

I have also been trying to track down some information on the pga_layout
table.  This appears to be a system table that might contain the
information I want but it does not list every table I have created.  Not
sure what that is.

The books I have do not say much if anything about such system tables. 

Any help or pointers would be appreciated.

 
-- 
Scot L. Harris <webid@cfl.rr.com>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 00:19  ljb <ljb220@mindspring.com>
  parent: Scot L. Harris <webid@cfl.rr.com>
  0 siblings, 3 replies; 11+ messages in thread

From: ljb @ 2004-05-17 00:19 UTC (permalink / raw)
  To: pgsql-php

webid@cfl.rr.com wrote:
> Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat
> 8.0 system.
>
> I am writing some php scripts where I want to generate a list of the
> column names in a particular table that the user selects.  I could take
> the brute force method and hard code the column names but then every
> time I add a new table or modify an existing one I would have to modify
> the code.  What I want is to have a generic function that given the
> table name it will pull the column names for my use.
>
> I need to get the table column names for several tables I have setup.  I
> know if I do a select * from tablename I can then use the pg_fieldname
> function to pull the column names for all columns. 
>
> But I don't think I want to select the entire contents of the table
> every time I want to get the names of the columns.  I know this will
> work but I think performance will be very poor.  
>...

You almost got it - just do "select * from tablename where 0=1", which returns
no rows but will give you the fieldnames. A portable and (I think)
efficient way to get table column names.



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 01:20  Stefan Gastaldon <stefang@bundaberg.qld.gov.au>
  parent: ljb <ljb220@mindspring.com>
  2 siblings, 0 replies; 11+ messages in thread

From: Stefan Gastaldon @ 2004-05-17 01:20 UTC (permalink / raw)
  To: pgsql-php



ljb wrote:

>webid@cfl.rr.com wrote:
>  
>
>>Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat
>>8.0 system.
>>
>>I am writing some php scripts where I want to generate a list of the
>>column names in a particular table that the user selects.  I could take
>>the brute force method and hard code the column names but then every
>>time I add a new table or modify an existing one I would have to modify
>>the code.  What I want is to have a generic function that given the
>>table name it will pull the column names for my use.
>>
>>I need to get the table column names for several tables I have setup.  I
>>know if I do a select * from tablename I can then use the pg_fieldname
>>function to pull the column names for all columns. 
>>
>>But I don't think I want to select the entire contents of the table
>>every time I want to get the names of the columns.  I know this will
>>work but I think performance will be very poor.  
>>...
>>    
>>
>
>You almost got it - just do "select * from tablename where 0=1", which returns
>no rows but will give you the fieldnames. A portable and (I think)
>efficient way to get table column names.
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>  
>
even more efficient (possibly not quite as portable but does work with 
pgsql and mysql)

select * from tablename limit 0





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 01:35  Scot L. Harris <webid@cfl.rr.com>
  parent: ljb <ljb220@mindspring.com>
  2 siblings, 0 replies; 11+ messages in thread

From: Scot L. Harris @ 2004-05-17 01:35 UTC (permalink / raw)
  To: ljb <ljb220@mindspring.com>; +Cc: pgsql-php

On Sun, 2004-05-16 at 20:19, ljb wrote:

> You almost got it - just do "select * from tablename where 0=1", which returns
> no rows but will give you the fieldnames. A portable and (I think)
> efficient way to get table column names.

Thanks.  That should do it.

I was not able to get to the list archives earlier.   

-- 
Scot L. Harris <webid@cfl.rr.com>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 02:55  Andrew McMillan <andrew@catalyst.net.nz>
  parent: ljb <ljb220@mindspring.com>
  2 siblings, 1 reply; 11+ messages in thread

From: Andrew McMillan @ 2004-05-17 02:55 UTC (permalink / raw)
  To: ljb <ljb220@mindspring.com>; webid@cfl.rr.com; +Cc: pgsql-php

On Mon, 2004-05-17 at 00:19 +0000, ljb wrote:
> >
> > I am writing some php scripts where I want to generate a list of the
> > column names in a particular table that the user selects.  I could take
> > the brute force method and hard code the column names but then every
> > time I add a new table or modify an existing one I would have to modify
> > the code.  What I want is to have a generic function that given the
> > table name it will pull the column names for my use.
> >
> > I need to get the table column names for several tables I have setup.  I
> > know if I do a select * from tablename I can then use the pg_fieldname
> > function to pull the column names for all columns. 
> >
> > But I don't think I want to select the entire contents of the table
> > every time I want to get the names of the columns.  I know this will
> > work but I think performance will be very poor.  
> >...
> 
> You almost got it - just do "select * from tablename where 0=1", which returns
> no rows but will give you the fieldnames. A portable and (I think)
> efficient way to get table column names.

It can be a cute trick, and I use it myself from time to time
(especially for "CREATE TABLE AS SELECT ..." where I want an empty table
with the same structure, pre v 7.4 which can do this anyway).  You
should be aware however that as written above it will almost invariably
force a full-table scan!

You can also select the column names from the database metadata
directly:

SELECT attname 
  FROM pg_class c join pg_attribute a on c.oid = a.attrelid
  WHERE c.relname = '<your table name>' 
    AND a.attnum >= 0;

This approach won't get killed by the efficiency problems above.

Cheers,
					Andrew.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267
           You have many friends and very few living enemies.
-------------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 03:16  Scot L. Harris <webid@cfl.rr.com>
  parent: Andrew McMillan <andrew@catalyst.net.nz>
  0 siblings, 2 replies; 11+ messages in thread

From: Scot L. Harris @ 2004-05-17 03:16 UTC (permalink / raw)
  To: pgsql-php

On Sun, 2004-05-16 at 22:55, Andrew McMillan wrote:
> On Mon, 2004-05-17 at 00:19 +0000, ljb wrote:
> > > But I don't think I want to select the entire contents of the table
> > > every time I want to get the names of the columns.  I know this will
> > > work but I think performance will be very poor.  
> > >...
> > 
> > You almost got it - just do "select * from tablename where 0=1", which returns
> > no rows but will give you the fieldnames. A portable and (I think)
> > efficient way to get table column names.
> 
> It can be a cute trick, and I use it myself from time to time
> (especially for "CREATE TABLE AS SELECT ..." where I want an empty table
> with the same structure, pre v 7.4 which can do this anyway).  You
> should be aware however that as written above it will almost invariably
> force a full-table scan!
> 
> You can also select the column names from the database metadata
> directly:
> 
> SELECT attname 
>   FROM pg_class c join pg_attribute a on c.oid = a.attrelid
>   WHERE c.relname = '<your table name>' 
>     AND a.attnum >= 0;
> 
> This approach won't get killed by the efficiency problems above.
> 
> Cheers,
> 					Andrew.

Thanks.  Most of the tables I have are fairly small (for now) but at
least one of them has many thousands of rows and I did not want to have
to scan all of them for this information.  I understand why the 0=1
trick will scan every row.  I like the idea of getting the meta data
directly.

None of the books I have seem to discuss this kind of thing.  Is the
pg_class and pg_attribute tables hidden?  I see pga_layout and some
others but not the first two when I do a \d.  I do get a column listing
when I do a \d pg_class so they are there.

And this worked great on my test database/tables.  

Thanks!



-- 
Scot L. Harris <webid@cfl.rr.com>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 10:26  Andrew McMillan <andrew@catalyst.net.nz>
  parent: Scot L. Harris <webid@cfl.rr.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Andrew McMillan @ 2004-05-17 10:26 UTC (permalink / raw)
  To: Scot L. Harris <webid@cfl.rr.com>; +Cc: pgsql-php

On Sun, 2004-05-16 at 23:16 -0400, Scot L. Harris wrote:
> > 
> > You can also select the column names from the database metadata
> > directly:
> > 
> > SELECT attname 
> >   FROM pg_class c join pg_attribute a on c.oid = a.attrelid
> >   WHERE c.relname = '<your table name>' 
> >     AND a.attnum >= 0;
> > 
> > This approach won't get killed by the efficiency problems above.
> > 
> > Cheers,
> > 					Andrew.
> 
> Thanks.  Most of the tables I have are fairly small (for now) but at
> least one of them has many thousands of rows and I did not want to have
> to scan all of them for this information.  I understand why the 0=1
> trick will scan every row.  I like the idea of getting the meta data
> directly.
> 
> None of the books I have seem to discuss this kind of thing.  Is the
> pg_class and pg_attribute tables hidden?  I see pga_layout and some
> others but not the first two when I do a \d.  I do get a column listing
> when I do a \d pg_class so they are there.
> 
> And this worked great on my test database/tables.  

When I want to figure out something like this I tend to use "psql -E" so
that all queries are echoed before being sent to the backend.  Then I do
something like "\d <table>" and see what SQL psql generates internally.

Also, dig here for detailed information on the postgresql data
dictionary tables:

http://www.postgresql.org/docs/7.4/interactive/catalogs.html

the most useful ones are pg_class and pg_attribute usually (for obvious
reasons :-).  With 7.4 I also find myself looking at the
pg_stat_activity view from time to time as well.

Cheers,
					Andrew.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267
      You possess a mind not merely twisted, but actually sprained.
-------------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 17:47  Steve Crawford <scrawford@pinpointresearch.com>
  parent: Scot L. Harris <webid@cfl.rr.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Steve Crawford @ 2004-05-17 17:47 UTC (permalink / raw)
  To: Scot L. Harris <webid@cfl.rr.com>; pgsql-php

On Sunday 16 May 2004 8:16 pm, Scot L. Harris wrote:
> On Sun, 2004-05-16 at 22:55, Andrew McMillan wrote:
> > On Mon, 2004-05-17 at 00:19 +0000, ljb wrote:
> > > > But I don't think I want to select the entire contents of the
> > > > table every time I want to get the names of the columns.  I
> > > > know this will work but I think performance will be very
> > > > poor.
> > >
> > > You almost got it - just do "select * from tablename where
> > > 0=1", which returns no rows but will give you the fieldnames. A
> > > portable and (I think) efficient way to get table column names.
> >
> > It can be a cute trick, and I use it myself from time to time
> > (especially for "CREATE TABLE AS SELECT ..." where I want an
> > empty table with the same structure, pre v 7.4 which can do this
> > anyway).  You should be aware however that as written above it
> > will almost invariably force a full-table scan!

Depending on your needs you can also use:
select * from tablename limit 0;

Slow machine, 3.3 million row table: 4 milliseconds. No full table 
scan there.

Cheers,
Steve




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-17 21:31  Scot L. Harris <webid@cfl.rr.com>
  parent: Andrew McMillan <andrew@catalyst.net.nz>
  0 siblings, 1 reply; 11+ messages in thread

From: Scot L. Harris @ 2004-05-17 21:31 UTC (permalink / raw)
  To: Andrew McMillan <andrew@catalyst.net.nz>; +Cc: pgsql-php

On Mon, 2004-05-17 at 06:26, Andrew McMillan wrote:
> On Sun, 2004-05-16 at 23:16 -0400, Scot L. Harris wrote:
> > > 
> > > You can also select the column names from the database metadata
> > > directly:
> > > 
> > > SELECT attname 
> > >   FROM pg_class c join pg_attribute a on c.oid = a.attrelid
> > >   WHERE c.relname = '<your table name>' 
> > >     AND a.attnum >= 0;
> > > 
> > > This approach won't get killed by the efficiency problems above.
> > > 
> > > Cheers,
> > > 					Andrew.
> > 

> When I want to figure out something like this I tend to use "psql -E" so
> that all queries are echoed before being sent to the backend.  Then I do
> something like "\d <table>" and see what SQL psql generates internally.
> 
> Also, dig here for detailed information on the postgresql data
> dictionary tables:
> 
> http://www.postgresql.org/docs/7.4/interactive/catalogs.html
> 
> the most useful ones are pg_class and pg_attribute usually (for obvious
> reasons :-).  With 7.4 I also find myself looking at the
> pg_stat_activity view from time to time as well.
> 
> Cheers,
> 					Andrew.


Thanks to everyone that responded to my question.  I have my application
working as I wanted.  I really appreciate all the help that was
provided.


-- 
Scot L. Harris <webid@cfl.rr.com>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-18 21:53  Robert Treat <xzilla@users.sourceforge.net>
  parent: Scot L. Harris <webid@cfl.rr.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Robert Treat @ 2004-05-18 21:53 UTC (permalink / raw)
  To: Scot L. Harris <webid@cfl.rr.com>; Andrew McMillan <andrew@catalyst.net.nz>; +Cc: pgsql-php

On Monday 17 May 2004 17:31, Scot L. Harris wrote:
> On Mon, 2004-05-17 at 06:26, Andrew McMillan wrote:
> > On Sun, 2004-05-16 at 23:16 -0400, Scot L. Harris wrote:
> > > > You can also select the column names from the database metadata
> > > > directly:
> > > >
> > > > SELECT attname
> > > >   FROM pg_class c join pg_attribute a on c.oid = a.attrelid
> > > >   WHERE c.relname = '<your table name>'
> > > >     AND a.attnum >= 0;
> > > >
> > > > This approach won't get killed by the efficiency problems above.
> > > >
> > > > Cheers,
> > > > 					Andrew.
> >
> > When I want to figure out something like this I tend to use "psql -E" so
> > that all queries are echoed before being sent to the backend.  Then I do
> > something like "\d <table>" and see what SQL psql generates internally.
> >
> > Also, dig here for detailed information on the postgresql data
> > dictionary tables:
> >
> > http://www.postgresql.org/docs/7.4/interactive/catalogs.html
> >
> > the most useful ones are pg_class and pg_attribute usually (for obvious
> > reasons :-).  With 7.4 I also find myself looking at the
> > pg_stat_activity view from time to time as well.
> >
> > Cheers,
> > 					Andrew.
>
> Thanks to everyone that responded to my question.  I have my application
> working as I wanted.  I really appreciate all the help that was
> provided.

A little late to the party, but it seems worth mentioning that this 
information is also available in the information_schema, which has the 
benefits of being sql complient, stable across releases, and keeps you out of 
the system catalogs.  for example:

cms74=# select column_name from information_schema.columns where table_name = 
'current_downloads';
 column_name 
-------------
 start_time
 entity_id
(2 rows)

for more on information schema check out 
http://www.postgresql.org/docs/7.4/interactive/information-schema.html

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: table column information
@ 2004-05-18 22:02  Scot L. Harris <webid@cfl.rr.com>
  parent: Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 0 replies; 11+ messages in thread

From: Scot L. Harris @ 2004-05-18 22:02 UTC (permalink / raw)
  To: pgsql-php

On Tue, 2004-05-18 at 17:53, Robert Treat wrote:
> A little late to the party, but it seems worth mentioning that this 
> information is also available in the information_schema, which has the 
> benefits of being sql complient, stable across releases, and keeps you out of 
> the system catalogs.  for example:
> 
> cms74=# select column_name from information_schema.columns where table_name = 
> 'current_downloads';
>  column_name 
> -------------
>  start_time
>  entity_id
> (2 rows)
> 
> for more on information schema check out 
> http://www.postgresql.org/docs/7.4/interactive/information-schema.html
> 
> Robert Treat

That looks like a nice way to get that information.  Thanks for another
good tip.


-- 
Scot L. Harris <webid@cfl.rr.com>




^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2004-05-18 22:02 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-16 23:18 table column information Scot L. Harris <webid@cfl.rr.com>
2004-05-17 00:19 ` ljb <ljb220@mindspring.com>
2004-05-17 01:20   ` Stefan Gastaldon <stefang@bundaberg.qld.gov.au>
2004-05-17 01:35   ` Scot L. Harris <webid@cfl.rr.com>
2004-05-17 02:55   ` Andrew McMillan <andrew@catalyst.net.nz>
2004-05-17 03:16     ` Scot L. Harris <webid@cfl.rr.com>
2004-05-17 10:26       ` Andrew McMillan <andrew@catalyst.net.nz>
2004-05-17 21:31         ` Scot L. Harris <webid@cfl.rr.com>
2004-05-18 21:53           ` Robert Treat <xzilla@users.sourceforge.net>
2004-05-18 22:02             ` Scot L. Harris <webid@cfl.rr.com>
2004-05-17 17:47       ` Steve Crawford <scrawford@pinpointresearch.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox