PostgreSQL SQL - in PlPgSQL function, how to use variable in a "select ... into ..

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

in PlPgSQL function, how to use variable in a "select ... into ..

Emi Lu

2006-03-17, 1:24 pm

Hello,

In pl/pgsql (postgresql 8.01), how to use variables in select .. into ..
command

CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
var1 ALIAS FOR $1;
cm_tableName tableA.col1%TYPE;
T1 VARCHAR := 'sourceTable';
query_value VARCHAR ;
BEGIN

SELECT col2 INTO cm_tableName FROM T1 WHERE col1 = var1 ;
EXECUTE query_value;

RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;

select test('abc');

Failed.

Also, tried "SELECT col2 INTO cm_tableName FROM || T1 WHERE col1 = ||
var1 " and
"SELECT col2 INTO cm_tableName FROM || T1 || WHERE col1 = || var1"

Failed as well.

T1 and var1 both are variables, may I how to use variables in a "select
.... into " query please?

Thanks a lot,
Ying

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links