Обсуждение: 'Select INTO" in Execute (dynamic query )

Поиск
Список
Период
Сортировка

'Select INTO" in Execute (dynamic query )

От
"Dinesh Pandey"
Дата:

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


 

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

От
Tom Lane
Дата:
"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 )

От
"Ramakrishnan Muralidharan"
Дата:
         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