From dpandey@secf.com Mon Apr 18 16:08:11 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 226D0534CF; Mon, 18 Apr 2005 13:08:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68780-04; Mon, 18 Apr 2005 16:08:02 +0000 (GMT) Received: from secf.com (209-128-104-244.bayarea.net [209.128.104.244]) by svr1.postgresql.org (Postfix) with ESMTP id A5165534BF; Mon, 18 Apr 2005 13:08:00 -0300 (ADT) Received: from dinesh by secf.com (MDaemon.PRO.v7.2.2.R) with ESMTP id md50000657488.msg; Mon, 18 Apr 2005 09:08:04 -0700 Reply-To: From: "Dinesh Pandey" To: , Subject: 'Select INTO" in Execute (dynamic query ) Date: Mon, 18 Apr 2005 21:34:48 +0530 Organization: Second Foundation India P Ltd. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008C_01C5445E.745B4400" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcVEMFg3GEx/uXK7Rcua1fxF+IhXbw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Authenticated-Sender: dpandey@secf.com X-Spam-Processed: mail.secf.com, Mon, 18 Apr 2005 09:08:04 -0700 (not processed: message from valid local sender) X-MDRemoteIP: 220.227.32.11 X-Return-Path: dpandey@secf.com X-MDAV-Processed: mail.secf.com, Mon, 18 Apr 2005 09:08:09 -0700 Message-Id: <20050418160800.A5165534BF@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.571 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_90_100, HTML_MESSAGE, MSGID_FROM_MTA_ID, UPPERCASE_25_50 X-Spam-Level: * X-Archive-Number: 200504/710 X-Sequence-Number: 76623 This is a multi-part message in MIME format. ------=_NextPart_000_008C_01C5445E.745B4400 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_008C_01C5445E.745B4400 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

        &nbs= p; vara    VARCHAR(10) :=3D'';

        &nbs= p; varb    VARCHAR(10) :=3D'';

          = result  VARCHAR(10) :=3D'Result';

          =

BEGIN  =          =

          = EXECUTE(

  =             &= nbsp;    'Select INTO vara, varb A1, A2 = from '|| $1

          = );

          =

RETURN result||': '|| vara ||' '|| varb;

 

END;

$$ LANGUAGE plpgsql;

 

 

Regards
Dinesh Pandey



 

------=_NextPart_000_008C_01C5445E.745B4400-- From tgl@sss.pgh.pa.us Mon Apr 18 17:26:53 2005 X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4A0B9535D8; Mon, 18 Apr 2005 14:26:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01822-04; Mon, 18 Apr 2005 17:26:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 34790533CB; Mon, 18 Apr 2005 14:26:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3IHQnGW023849; Mon, 18 Apr 2005 13:26:50 -0400 (EDT) To: dpandey@secf.com Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: 'Select INTO" in Execute (dynamic query ) In-reply-to: <20050418160800.A5165534BF@svr1.postgresql.org> References: <20050418160800.A5165534BF@svr1.postgresql.org> Comments: In-reply-to "Dinesh Pandey" message dated "Mon, 18 Apr 2005 21:34:48 +0530" Date: Mon, 18 Apr 2005 13:26:49 -0400 Message-ID: <23848.1113845209@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/723 X-Sequence-Number: 76636 "Dinesh Pandey" 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 From ramakrishnanm@pervasive-postgres.com Wed Apr 20 10:21:35 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D1A6C537A8 for ; Wed, 20 Apr 2005 07:21:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14496-08 for ; Wed, 20 Apr 2005 10:21:32 +0000 (GMT) Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by svr1.postgresql.org (Postfix) with ESMTP id C6BE153795 for ; Wed, 20 Apr 2005 07:21:28 -0300 (ADT) Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Wed, 20 Apr 2005 05:21:31 -0500 Received: from ausmail2k3.aus.pervasive.com ([172.16.4.8]) by ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 20 Apr 2005 05:21:30 -0500 Received: from BLRMAIL1.aus.pervasive.com ([10.3.16.60]) by ausmail2k3.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 20 Apr 2005 05:21:30 -0500 X-MIMEOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message Subject: Re: 'Select INTO" in Execute (dynamic query ) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C54592.B558D28B" Date: Wed, 20 Apr 2005 15:51:25 +0530 Message-ID: <02767D4600E59A4487233B23AEF5C5992A407C@blrmail1.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] 'Select INTO" in Execute (dynamic query ) Thread-Index: AcVEMFg3GEx/uXK7Rcua1fxF+IhXbwBYTydQ From: "Ramakrishnan Muralidharan" To: , X-OriginalArrivalTime: 20 Apr 2005 10:21:30.0195 (UTC) FILETIME=[B7D97230:01C54592] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.263 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200504/335 X-Sequence-Number: 21228 This is a multi-part message in MIME format. ------_=_NextPart_001_01C54592.B558D28B Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 It is not possible to access a local variable in EXECUTE = Command and give a syntax error. FOR..IN..LOOP is the best option =20 CREATE OR REPLACE FUNCTION TestQry( vCon teXt ) RETURNS VARCHAR AS $$ DECLARE var1 varchar(10); var2 varchar(10); result varchar( 20 );=20 rRec RECORD;=20 BEGIN =20 FOR rRec IN EXECUTE( 'SELECT A1,A2 FROM '||vCon ) LOOP var1 =3D rRec.A1; var2 =3D rRec.A2; END LOOP; =20 RETURN VAR1||VAR2; =20 END; $$ LANGUAGE 'plpgsql'; =20 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 =20 What's wrong with this code (ERROR: syntax error at or near "INTO" at = character 8)? =20 Problem: I want to put A1, A2 values in two variables vara, varb. =20 CREATE OR REPLACE FUNCTION test(text)=20 RETURNS VARCHAR AS $$ Declare vara VARCHAR(10) :=3D''; varb VARCHAR(10) :=3D''; result VARCHAR(10) :=3D'Result'; =20 BEGIN =20 EXECUTE( 'Select INTO vara, varb A1, A2 from '|| $1=20 ); =20 RETURN result||': '|| vara ||' '|| varb; =20 END; $$ LANGUAGE plpgsql; =20 =20 Regards Dinesh Pandey =20 ------_=_NextPart_001_01C54592.B558D28B Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
         = Hi,
 
          &nbs= p; It is=20 not possible to access a local variable in EXECUTE Command and give a = syntax=20 error. FOR..IN..LOOP is the best option
          &nbs= p; 
          &nbs= p; CREATE=20 OR REPLACE FUNCTION TestQry( vCon teXt=20 )
            = RETURNS=20 VARCHAR AS=20 $$
            = DECLARE
          &n= bsp;   =20 var1=20 varchar(10);
         &nb= sp;    =20 var2=20 varchar(10);
         &nb= sp;    =20 result varchar( 20=20 ); 
          &= nbsp;   =20 rRec =20 RECORD; 
         &n= bsp; =20 BEGIN
   
       =          =20 FOR rRec IN EXECUTE( 'SELECT A1,A2 FROM  '||vCon )=20 LOOP
           = ;           =20 var1 =3D=20 rRec.A1;
          &= nbsp;           =20 var2 =3D=20 rRec.A2;
          &= nbsp;     =20 END=20 LOOP;
  
        =       =20 RETURN=20 VAR1||VAR2;
   
      =      =20 END;
           = ; $$=20 LANGUAGE 'plpgsql';
 
        =20 Regards,
        =20 R.Muralidharan.
-----Original Message-----
From: Dinesh Pandey=20 [mailto:dpandey@secf.com]
Sent: Monday, April 18, 2005 9:35=20 PM
To: pgsql-sql@postgresql.org;=20 pgsql-general@postgresql.org
Subject: [SQL] 'Select INTO" in = Execute=20 (dynamic query )

Hi

 

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

 

Problem: I want to put = A1, A2=20 values in two variables vara, varb.

 

CREATE OR REPLACE = FUNCTION=20 test(text)

RETURNS VARCHAR AS=20 $$

Declare

         =20 vara    VARCHAR(10) :=3D'';

         =20 varb    VARCHAR(10) :=3D'';

 =20         result  VARCHAR(10)=20 :=3D'Result';

 =20         =

BEGIN =20          =

 =20        =20 EXECUTE(

 =20 =             &= nbsp;   =20 'Select INTO vara, varb = A1, A2 from=20 '|| $1

 =20         = );

 =20         =

RETURN result||': = '|| vara ||' = '||=20 varb;

 

END;

$$ LANGUAGE=20 plpgsql;

 

 

Regards
Dinesh=20 Pandey



 

------_=_NextPart_001_01C54592.B558D28B--