Re: 'Select INTO" in Execute (dynamic query )
| От | Ramakrishnan Muralidharan |
|---|---|
| Тема | Re: 'Select INTO" in Execute (dynamic query ) |
| Дата | |
| Msg-id | 02767D4600E59A4487233B23AEF5C5992A407C@blrmail1.aus.pervasive.com обсуждение исходный текст |
| Ответ на | 'Select INTO" in Execute (dynamic query ) ("Dinesh Pandey" <dpandey@secf.com>) |
| Список | pgsql-sql |
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';
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@secf.com]
Sent: Monday, April 18, 2005 9:35 PM
To: pgsql-sql@postgresql.org; pgsql-general@postgresql.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
В списке pgsql-sql по дате отправления: