'Select INTO" in Execute (dynamic query )

Started by Dinesh Pandeyalmost 21 years ago2 messagesgeneral
Jump to latest
#1Dinesh Pandey
dpandey@secf.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dinesh Pandey (#1)
Re: 'Select INTO" in Execute (dynamic query )

"Dinesh Pandey" <dpandey@secf.com> writes:

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

You can't use plpgsql's SELECT INTO in an EXECUTE'd command, because
SELECT INTO means something entirely different to the main SQL engine.

The usual workaround is to use FOR ... IN EXECUTE. See the plpgsql docs.

regards, tom lane