Re: How to query by column names - Mailing list pgsql-sql

So why are you avoiding "SELECT * FROM t1;" ?

You'd probably also be happier using information_schema to get the column 
names.

On Mon, 22 Jan 2007, Richard Ray wrote:

> All attributes of t1
> Where (select attname from pg_attribute where attrelid = (select relfilenode 
> from pg_class where relname = 't1') and attisdropped = false and attnum > 0)
> is a substitute for *
>
> On Mon, 22 Jan 2007, Jeff Frost wrote:
>
>> Perhaps I should have asked this earlier.  What information are you trying 
>> to extract?
>> 
>> On Mon, 22 Jan 2007, Richard Ray wrote:
>> 
>>> This is not exactly what I need
>>> I want to return the data in t1
>>> 
>>> On Mon, 22 Jan 2007, Jeff Frost wrote:
>>> 
>>>> I think this is what you're looking for Richard:
>>>> 
>>>> SELECT attname FROM pg_attribute pa, pg_class pc
>>>>  WHERE pc.relname = 't1'
>>>>    AND pa.attrelid = pc.relfilenode
>>>>    AND pa.attisdropped IS FALSE
>>>>    AND pa.attnum > 0;
>>>> 
>>>> Let me know if it doesn't do what you intended.
>>>> 
>>>> On Mon, 22 Jan 2007, Richard Ray wrote:
>>>> 
>>>>> This may be a simple but can I create a query such as
>>>>> 
>>>>> select (select attname from pg_attribute where attrelid = (select 
>>>>> relfilenode from pg_class where relname = 't1') and attisdropped = false 
>>>>> and attnum > 0) from t1;
>>>>> 
>>>>> I get
>>>>> ERROR:  more than one row returned by a subquery used as an expression
>>>>> 
>>>>> Thanks
>>>>> Richard
>>>>> 
>>>>> ---------------------------(end of broadcast)---------------------------
>>>>> TIP 5: don't forget to increase your free space map settings
>>>>> 
>>>>> 
>>>> 
>>>> -- 
>>>> Jeff Frost, Owner     <jeff@frostconsultingllc.com>
>>>> Frost Consulting, LLC     http://www.frostconsultingllc.com/
>>>> Phone: 650-780-7908    FAX: 650-649-1954
>>>> 
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>>>      subscribe-nomail command to majordomo@postgresql.org so that your
>>>>      message can get through to the mailing list cleanly
>>>> 
>>> 
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>>      choose an index scan if your joining column's datatypes do not
>>>      match
>>> 
>>> 
>> 
>> -- 
>> Jeff Frost, Owner     <jeff@frostconsultingllc.com>
>> Frost Consulting, LLC     http://www.frostconsultingllc.com/
>> Phone: 650-780-7908    FAX: 650-649-1954
>> 
>
>

-- 
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954