X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 2448DCCD51 for ; Thu, 10 Jul 2003 12:11:08 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 21262-04 for ; Thu, 10 Jul 2003 09:10:37 -0300 (ADT) Received: from kin281.voice2kinera.com (unknown [202.63.107.28]) by svr1.postgresql.org (Postfix) with ESMTP id 5D985CCE31 for ; Thu, 10 Jul 2003 06:56:43 -0300 (ADT) Received: from vijay [202.63.107.19] by kin281.voice2kinera.com (SMTPD32-7.13 EVAL) id AA4CEF01A6; Thu, 10 Jul 2003 15:35:00 +0530 Message-ID: <000c01c346ca$7fc0b660$cf01a8c0@vijay> From: "Vijay Kumar" To: Subject: problem with temporary table. Date: Thu, 10 Jul 2003 15:33:23 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0009_01C346F8.99743770" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/148 X-Sequence-Number: 14262 This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C346F8.99743770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, We are using postgresql7.3.3, we are encountering some problems by usin= g temporary tables. Actually our requirement was,=20 1. create temporary table. 2. insert some values on that table by using some quries. 3. select the inserted values from the temporary table. To fullfil the above requirement, we wrote the below functions..kindly go t= hrough the below functions and=20 guide us to come out from this temporary table problem. 1. Call the same function more than ones in the same connection. eg; create or replace function TestTemp_refcur(refcursor) returns refcu= rsor as ' declare refc alias for $1; begin create temporary table temp_table(idno numeric,iname varcha= r(10)); insert into temp_table values (1,''ganesh''); insert into temp_table values (2,''John''); open refc for select * from test_temp_table; return refc; end; ' language 'plpgsql'; begin; select TestTemp_refcur('funcursor'); fetch all in funcursor; commit; The above function is working fine for the first call, from next ca= ll onwards it is throwing the below error. Error: relation 'temp_table' already exists. 2. To avoid this, we had gone through some of postgresql faq and documents.= There some one suggested to create temporary table by Execute. So that, we created one function, inside that fucntion we created one mor= e function to take care of creating temporary table.=20 Eg, create or replace function TestTemp_refcur(refcursor) returns refcursor= As ' declare refc alias for $1; lString Varchar(4000); begin lString :=3D '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS v= archar as '''' BEGIN ''; lString :=3D lString || '' create temporary table temp_Ta= ble( Name Numeric);''; lString :=3D lString || '' insert into temp_Table values (1= ); ''; lString :=3D lString || '' insert into temp_Table values (2= );''; lString :=3D lString || '' return null; end;'''' language '= '''plpgsql'''';''; raise notice '' Notice is % '', lString; execute lString; open refc for select * from temp_Table; return refc; end; ' language 'plpgsql'; begin; select TestTemp_refcur('funcursor'); fetch all in funcursor; commit; With the above approach, we are getting the below error. Error : Relation "temp_table" does not exist Any kind of info/soln/help will be highly appreciated.. Thanks & Regards Vijay ------=_NextPart_000_0009_01C346F8.99743770 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
    We are using postgresql= 7.3.3, we=20 are encountering some problems by using temporary tables.
 
Actually our requirement was,
        1. c= reate=20 temporary table.
        2. i= nsert=20 some values on that table by using some quries.
       = 3.=20 select the inserted values from the temporary table.
 
To fullfil the above requirement, we wrote= the=20 below functions..kindly go through the below functions and
guide us to come out from this temporary t= able=20 problem.
 
 
1. Call the same function more than o= nes in=20 the same connection.
 
    eg;
        create or replace function TestTemp_refcur(refcursor) retur= ns=20 refcursor as '
     &= nbsp; =20 declare
     &= nbsp; =20         refc alias for $1;
         begin=
     &= nbsp; =20         create temporary table temp_table(idn= o=20 numeric,iname varchar(10));
     &= nbsp; =20         insert into temp_table values=20 (1,''ganesh'');
     &= nbsp; =20         insert into temp_table values=20 (2,''John'');
     &= nbsp; =20         open refc for select * from=20 test_temp_table;
     &= nbsp; =20         return refc;
   =20      end;
         ' language=20 'plpgsql';
 
    &n= bsp; =20=20 begin;
        selec= t=20 TestTemp_refcur('funcursor');
        fetc= h all=20 in funcursor;
       =20 commit;
 
        The = above=20 function is working fine for the first call, from next call onwards it is= =20 throwing the below error.
     &= nbsp; =20 Error: relation 'temp_table' already exists.
 
2. To avoid this, we had gone through some= =20 of postgresql faq and documents. There some one suggested to create=20 temporary table by Execute.
 So that,  we created one functi= on,=20 inside that fucntion we created one more function to take care of creating= =20 temporary table.
 
Eg,
    create = or replace=20 function TestTemp_refcur(refcursor) returns refcursor As '
  &= nbsp;=20 declare
             r= efc=20 alias for $1;
          &nb= sp;=20 lString Varchar(4000);
   =20 begin
            lStr= ing=20 :=3D '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as ''''
 
            BEGI= N=20 '';
           =20     lString  :=3D lString ||  '' create temporary = table=20 temp_Table( Name Numeric);'';
 
           =20     lString :=3D lString || '' insert into temp_Table values= (1);=20 '';
 
           =20     lString :=3D lString || '' insert into temp_Table values= =20 (2);'';
 
           =20     lString :=3D lString || '' return null; end;'''' languag= e=20 ''''plpgsql'''';'';
 
           =20     raise notice '' Notice is % '',=20 lString;
           = =20     execute=20 lString;
          &nb= sp;    =20 open refc for select * from=20 temp_Table;
          =      =20 return refc;
    end;
    ' language=20 'plpgsql';
 
   =20 begin;
    select=20 TestTemp_refcur('funcursor');
    fetch all in=20 funcursor;
    commit;
 
    With the above approach, we are getting the below= =20 error.
    = Error :=20 Relation "temp_table" does not exist
    Any kind of info/soln/help will be highly=20 appreciated..
 
Thanks & Regards
Vijay
 
 
 
------=_NextPart_000_0009_01C346F8.99743770--