X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 200A09DC8A8 for ; Fri, 17 Mar 2006 17:36:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03408-10 for ; Fri, 17 Mar 2006 17:36:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from eastrmmtao01.cox.net (eastrmmtao01.cox.net [68.230.240.38]) by postgresql.org (Postfix) with ESMTP id 9E0F39DC8E0 for ; Fri, 17 Mar 2006 17:36:09 -0400 (AST) Received: from [192.168.0.8] (really [24.136.35.185]) by eastrmmtao01.cox.net (InterMail vM.6.01.05.02 201-2131-123-102-20050715) with ESMTP id <20060317213604.PBFH4894.eastrmmtao01.cox.net@[192.168.0.8]>; Fri, 17 Mar 2006 16:36:04 -0500 From: Robert Treat To: pgsql-sql@postgresql.org, emilu@encs.concordia.ca Subject: Re: in PlPgSQL function, how to use variable in a "select ... Date: Fri, 17 Mar 2006 16:35:35 -0500 User-Agent: KMail/1.8.3 Cc: "A. Kretschmer" References: <441AF775.7060006@encs.concordia.ca> <20060317195031.GA8508@webserv.wug-glas.de> <441B1D0E.3000109@encs.concordia.ca> In-Reply-To: <441B1D0E.3000109@encs.concordia.ca> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200603171635.35661.xzilla@users.sourceforge.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.118 required=5 tests=[AWL=0.118] X-Spam-Score: 0.118 X-Spam-Level: X-Archive-Number: 200603/255 X-Sequence-Number: 24601 On Friday 17 March 2006 15:33, Emi Lu wrote: > >>Does not work either, the whole function is: > >> > >>create table t1(col1 varchar(3), col2 varchar(100)); > >>insert into t1 values('001', 'Result 1'); > >>insert into t1 values('002', 'Result 2'); > >>insert into t1 values('003', 'Result 3'); > >> > >>CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$ > >>DECLARE > >> col1_value ALIAS FOR $1; > >>cm_tableName st1_legend.code_map_tablename%TYPE; > >>lengendTableName VARCHAR := 't1'; > >> query_value VARCHAR ; > >>BEGIN > >> > >> SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 = > >>col1_value ; > > > >This can't work, read the docu: > >http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLP > >GSQL-STATEMENTS-EXECUTING-DYN > > > >You should build a string with your SQL and EXECUTE this string. > > Thank you Andreas. Unfortunately it did not work. maybe I made something > wrong? > > drop table t1; > create table t1(col1 varchar(3), col2 varchar(100)); > insert into t1 values('001', 'Result 1'); > insert into t1 values('002', 'Result 2'); > insert into t1 values('003', 'Result 3'); > > CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$ > DECLARE > col1_value ALIAS FOR $1; > cm_tableName st1_legend.code_map_tablename%TYPE; > lengendTableName VARCHAR := 't1'; > query_value VARCHAR ; > BEGIN > query_value := 'SELECT col2 FROM lengendTableName WHERE col1 = \'' || > col1_value || '\''; > > EXECUTE query_value INTO cm_tableName; > > RETURN cm_tableName; > END; > $$ language 'plpgsql' IMMUTABLE STRICT; > select test('001'); > This function would work on 8.1, provided you created the sql statement correctly: query_value := 'SELECT col2 FROM ' ||lengendTableName||' WHERE col1 = \'' || col1_value || '\''; > > I am using postgresql 8.0.1, and I am afraid that 8.0 does not support > "excecute ... into ...." > In which case you could use: FOR cm_tableName IN EXECUTE query_value LOOP RETURN cm_tableName END LOOP which is a little hacky, though you could use a second variable for assignment if you felt strongly about it. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL