Re: 'Select INTO" in Execute (dynamic query )

Hi,

It is not possible to access a local variable in EXECUTE Command and give a syntax error. FOR..IN..LOOP is the best option

CREATE OR REPLACE FUNCTION TestQry( vCon teXt )
RETURNS VARCHAR AS $$
DECLARE
var1 varchar(10);
var2 varchar(10);
result varchar( 20 );
rRec RECORD;
BEGIN

FOR rRec IN EXECUTE( 'SELECT A1,A2 FROM '||vCon ) LOOP
var1 = rRec.A1;
var2 = rRec.A2;
END LOOP;

RETURN VAR1||VAR2;

END;
$$ LANGUAGE 'plpgsql';

Regards,
R.Muralidharan.

-----Original Message-----
From: Dinesh Pandey [mailto:dpandey(at)secf(dot)com]
Sent: Monday, April 18, 2005 9:35 PM
To: pgsql-sql(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
Subject: [SQL] 'Select INTO" in Execute (dynamic query )

Hi

What's wrong with this code (ERROR: syntax error at or near "INTO" at character 8)?

Problem: I want to put A1, A2 values in two variables vara, varb.

CREATE OR REPLACE FUNCTION test(text)

RETURNS VARCHAR AS $$

Declare

vara VARCHAR(10) :='';

varb VARCHAR(10) :='';

result VARCHAR(10) :='Result';

BEGIN

EXECUTE(

'Select INTO vara, varb A1, A2 from '|| $1

);

RETURN result||': '|| vara ||' '|| varb;

END;

$$ LANGUAGE plpgsql;

Regards
Dinesh Pandey