Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 99F2C9FB518 for ; Mon, 22 Jan 2007 20:30:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 37399-10 for ; Mon, 22 Jan 2007 20:30:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from itspmx2.state.ms.us (itspmx2.state.ms.us [205.144.224.112]) by postgresql.org (Postfix) with ESMTP id 7C4B39FA617 for ; Mon, 22 Jan 2007 20:30:50 -0400 (AST) Received: from rray.drdc.mstc.ms.gov ([10.3.192.2]) by itspmx2.state.ms.us (8.13.4/8.13.4) with ESMTP id l0N0UX7t023937 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 22 Jan 2007 18:30:39 -0600 (CST) Received: from rray.drdc.mstc.ms.gov (localhost.localdomain [127.0.0.1]) by rray.drdc.mstc.ms.gov (8.13.8/8.13.6) with ESMTP id l0N0UX2r011114 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 22 Jan 2007 18:30:33 -0600 Received: from localhost (rray@localhost) by rray.drdc.mstc.ms.gov (8.13.8/8.13.8/Submit) with ESMTP id l0N0UXUO011111; Mon, 22 Jan 2007 18:30:33 -0600 X-Authentication-Warning: rray.drdc.mstc.ms.gov: rray owned process doing -bs Date: Mon, 22 Jan 2007 18:30:33 -0600 (CST) From: Richard Ray X-X-Sender: rray@rray.drdc.mstc.ms.gov To: Jeff Frost cc: pgsql-sql@postgresql.org Subject: Re: How to query by column names In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/106 X-Sequence-Number: 27258 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 >>> 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 > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 >