table column information

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: table column information

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



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: table column information

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: table column information

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 = ''
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.
------------------------------------------------------------ -------------


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: table column information

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 = ''
> 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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: table column information

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 = ''
> > 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