pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed'Select INTO" in Execute (dynamic query )
3+ messages / 3 participants
[nested] [flat]
* 'Select INTO" in Execute (dynamic query )
@ 2005-04-18 16:04 Dinesh Pandey <dpandey@secf.com>
2005-04-18 17:26 ` Re: 'Select INTO" in Execute (dynamic query ) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Dinesh Pandey @ 2005-04-18 16:04 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 'Select INTO" in Execute (dynamic query )
2005-04-18 16:04 'Select INTO" in Execute (dynamic query ) Dinesh Pandey <dpandey@secf.com>
@ 2005-04-18 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-04-18 17:26 UTC (permalink / raw)
To: dpandey@secf.com; +Cc: pgsql-sql; pgsql-general@postgresql.org
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 'Select INTO" in Execute (dynamic query )
@ 2005-04-20 10:21 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-04-20 10:21 UTC (permalink / raw)
To: 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';
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-04-20 10:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-18 16:04 'Select INTO" in Execute (dynamic query ) Dinesh Pandey <dpandey@secf.com>
2005-04-18 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-20 10:21 Re: 'Select INTO" in Execute (dynamic query ) Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox