Home > mailing lists

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

'Select INTO" in Execute (dynamic query )

From

"Dinesh Pandey"

Date:

18 April 2005, 13:08:15

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

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

From

Tom Lane

Date:

18 April 2005, 14:26:55

"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

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

From

"Ramakrishnan Muralidharan"

Date:

23 April 2005, 01:48:55

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