Re: Stored procedure error

Hello

You cannot to drop temporary table in stored procedure. Then you can
have a problem. There are two possibilities: a) you will upgrade to
8.3, b) you will change code - minimum is replace drop table by
truncate table

please, read
http://www.postgres.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL

Regards
Pavel Stehule

p.s. this isn't bug - please use pg_general mailing_list

2009/11/19 Valaki Valahol <ozoltan9(at)hotmail(dot)com>:
> Dear Sirs,
>
> My name is Zoltán Ötvös and I'm from Hungary.
> Im using PostgreSQL database server from many years and I'm very satisfied
> with this
> database.
> I'm wanted to write a little financial routine in Linux under Lazarus but
> for the first I've
> tested it under pgAdmin. Because I didn't wanted to overload the network
> traffic with
> data manipulation I've thought that make all the data manipulation routine
> on the server
> side in a stored procedure. The main goal is to create the MoneyTable table
> dynamically
> every time and fill it with the actual data. I have to create the table
> every time, because
> its structure, the number of the columns and rows may vary every time. Here
> are the
> two stored procedures I wrote:
>
> /****************************************************************/
> /*                                         create the emtpy table
>                                       */
> /****************************************************************/
>
> drop  function CreateMoneyTable();
>
> create function CreateMoneyTable()
>    returns integer as
> $$
> declare
>   cmt integer;
>   NewId integer;
>
> begin
>  cmt:=0;
>
>  begin
>   drop table MoneyTable;
>
>   cmt:=1;
>  exception
>   when Others then
>    cmt:=-1;
>  end;
>
>  begin
>   select Max(mtid) into NewId
>   from MoneyTable;
>
>   cmt:=2;
>  exception
>   when Others then
>    cmt:=-2;
>  end;
>
>  if cmt=-2 then
>    begin
>     create global temporary table MoneyTable (
>      mtid serial,
>      mtdate char(10),
>      primary key (mtid)
>      )
>     on commit preserve rows;
>
>     cmt:=3;
>    exception
>     when Others then
>      cmt:=-3;
>    end;
>  end if;
>
>  return cmt;
> end;
> $$
>  LANGUAGE 'plpgsql' VOLATILE;
>
>
> /****************************************************************/
> /*                                      fill the table with start data
>                               */
> /****************************************************************/
>
> drop  function FillMoneyTable(d1 char(10), d2 char(10));
>
> create function FillMoneyTable(d1 char(10), d2 char(10))
>    returns integer as
> $$
> declare
>   cmt integer;
>   i integer;
>   dt0 date;
>   dt1 date;
>   NewId integer;
>
> begin
>  cmt:=0;
>
>  begin
>   select Max(MTID) into NewId
>   from MoneyTable;
>
>   cmt:=1;
>  exception
>   when Others then
>    cmt:=-1;
>  end;
>
>  if cmt = 1 then
>    dt0:=to_date(d1,'YYYY-MM-DD');
>    dt1:=to_date(d2,'YYYY-MM-DD');
>
>    i:=0;
>    while ((dt0+i)<=dt1) loop
>      insert into MoneyTable (MTDATE) values
>          (to_char(dt0+i,'YYYY-MM-DD'));
>
>     i:=i+1;
>    end loop;
>  end if;
>
>  return cmt;
> end;
> $$
>  LANGUAGE 'plpgsql' VOLATILE;
>
> /****************************************************************/
> /*                                             check if it works
>                                     */
> /****************************************************************/
>
> select CreateMoneyTable();
>
> select FillMoneyTable('2009-09-01','2009-09-13');
>
> select * from MoneyTable;
>
> /****************************************************************/
>
> Everything works fine for the first time. But if I call it for the second
> time in the same
> query window under pgAdmin then it gives the following error messages:
>
>
> ERROR:  relation with OID 28461 does not exist
> CONTEXT:  SQL statement "INSERT INTO MoneyTable (MTDATE) values (to_char( $1
> + $2 ,'YYYY-MM-DD'))"
> PL/pgSQL function "fillmoneytable" line 18 at SQL statement
>
>
> ********** Error **********
>
> ERROR: relation with OID 28461 does not exist
> SQL state: 42P01
> Context: SQL statement "INSERT INTO MoneyTable (MTDATE) values (to_char( $1
> + $2 ,'YYYY-MM-DD'))"
> PL/pgSQL function "fillmoneytable" line 18 at SQL statement
>
>
> I can't understand that why it returns with error. It seems that for the
> second time the create table
> command it's unable to complete when it gets to the insert command so the
> insert command doesn't
> finds the target table. Like the database server internal execution it
> should be too fast, I guess...
> Naturally I've tried all these scripts as the postgres superuser.
> After filling the dates in this temporary table I would like to add the
> columns which contains the
> financial datas.
> I would prefer to use temporary tables, because this program it's used in a
> network enviroment.
> So my first question is that what do I make wrong that I can't run these
> scripts for many times?
> Second question is that how it should work ? What's the solution ?
>
> Please help!....
>
> Thank You for Your help in advance!
>
> Best regards
> Zoltán Ötvös
> Hungary
>
>
>
>
> ________________________________
> Windows Live: Make it easier for your friends to see what you’re up to on
> Facebook.