Re: find column names from query

----- Original Message -----
From: "Michael Fuhr" <mike(at)fuhr(dot)org>
To: "Afton & Ray Still" <rastill(at)shaw(dot)ca>
Cc: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, January 24, 2005 1:42 AM
Subject: Re: [NOVICE] find column names from query

> On Sun, Jan 23, 2005 at 11:46:15PM -0700, Afton & Ray Still wrote:
>
>> going through the documentation I found the following:
>>
>> SELECT attname::regclass FROM pg_attribute WHERE attrelid =
>> travel::regclass
>
> Are you sure the example looked like that?

The original example was:
SELECT attrelid::regclass, array_accum(attname)
FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
GROUP BY attrelid;but (copied from above) I cut it down toSELECT
attname::regclass FROM pg_attribute WHERE attrelid = travel::regclass (oops,
missed the ''. I also used a different table name.as found
at:http://www.postgresql.org/docs/8.0/interactive/xaggr.html

> attname is a name type
> and shouldn't be cast to regclass, and "travel" should be in single
> quotes if it's a table name. Try this:
>
> SELECT attname FROM pg_attribute WHERE attrelid = 'travel'::regclass;
>
> Here's something a little more useful:
>
> SELECT attname
> FROM pg_attribute
> WHERE attrelid = 'travel'::regclass
> AND attisdropped IS FALSE
> AND attnum >= 1
> ORDER BY attnum;
>
> If you're using PostgreSQL 7.4 or later then you could also use the
> Information Schema; see the documentation for details.
>
> SELECT column_name
> FROM information_schema.columns
> WHERE table_name = 'travel'
> ORDER BY ordinal_position;
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.7.1 - Release Date: 1/19/2005
>

Ray

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.7.1 - Release Date: 1/19/2005