Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 548569F9B05 for ; Mon, 18 Dec 2006 17:55:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 78024-04 for ; Mon, 18 Dec 2006 17:55:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mx1.ipf.is (mack.ipf.is [85.197.192.164]) by postgresql.org (Postfix) with ESMTP id 1B35B9F9398 for ; Mon, 18 Dec 2006 17:55:01 -0400 (AST) Received: (qmail 2070 invoked by uid 1010); 18 Dec 2006 21:51:28 +0000 Received: from 88.149.106.19 by mack (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms. Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 6.60402 secs); 18 Dec 2006 21:51:28 -0000 X-Antivirus-HIVE-Mail-From: gnari@hive.is via mack X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):. Processed in 6.60402 secs Process 2027) Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19) by mx1.ipf.is with SMTP; 18 Dec 2006 21:51:21 +0000 Subject: Re: SELECT with WHERE clause by column number From: Ragnar To: Carlos Santos Cc: Lista PostgreSQL SQL In-Reply-To: <20061218171739.98671.qmail@web57105.mail.re3.yahoo.com> References: <20061218171739.98671.qmail@web57105.mail.re3.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1 Date: Mon, 18 Dec 2006 21:56:28 +0000 Message-Id: <1166478988.6369.238.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/132 X-Sequence-Number: 27088 On mán, 2006-12-18 at 09:17 -0800, Carlos Santos wrote: > > SELECT id FROM myTable WHERE column-number = 'value'; > (PS: The id column is the primary key of myTable). > > That is a select using column number in the WHERE clause what don't > exists in SQL. > > I need this because there's a situation in my program where I don't > have the column name. > I've solved that querying Postgresql the name of the column with that > number, and then creating the SELECT query. > But this solution is slow... two database calls... > > Can I do this with a single query or in a faster way through SQL, an > internal function or through a Procedural Language? this can be done with the more dynamic prodedural languages. this should be possible too with pl/pgsql using your first query + EXECUTE this is still 2 queries, but only one client round-trip. this can also be done in one SQL query with some creative misuse of an UNION ALL within a subquery, if you know the number of columns in your table. gnari