If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		:Christian Zagrodnick	
Your email address	:cz@gocept.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	:Intel Pentium

  Operating System (example: Linux 2.0.26 ELF) 	:Linux 2.4.18-686-smp

  PostgreSQL version (example: PostgreSQL-7.2.1):   PostgreSQL-7.2.1

  Compiler used (example:  gcc 2.95.2)		:gcc 2.95.4 (i think; i used the debian package)


Please enter a FULL description of your problem:
------------------------------------------------
I built a plpgsql function that behaves strange. Starting a new session it
works one time. Dumping the function in the same session again let it work as
it is supposed to.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
----------------------------------------------------------------------

create table mystifier (
        rd int8 check (1::bool and 0::bool) -- table has to be empty
);

create or replace function mystify(int8) returns varchar as '
        DECLARE
                id ALIAS for $1;
                random int8;
                rec RECORD;
                newid varchar;
                qry varchar;
        BEGIN
        
        SELECT INTO rec * FROM mystifier;
        IF NOT FOUND THEN
                qry:=''SELECT (random()*500000000)::int8 as rd''; 
                FOR rec IN EXECUTE qry LOOP
                        random:=rec.rd;
                END LOOP;
                CREATE TEMP TABLE mystifier (rd int8);
                INSERT INTO mystifier VALUES (rec.rd);
                SELECT INTO rec * FROM mystifier;
        END IF; 
        
        random:=rec.rd;
                
        newid:=to_char(id#random,''0000000000000000000'');
        RETURN newid;
        END;
' language 'plpgsql';


chzeamt=> select * from mystifier ;
 rd 
----
(0 rows)

chzeamt=> select mystify(3);
       mystify        
----------------------
  0000000000147097331
(1 row)

chzeamt=> select mystify(3);
NOTICE:  Error occurred while executing PL/pgSQL function mystify
NOTICE:  line 15 at SQL statement
ERROR:  Relation 'mystifier' already exists

chzeamt=> create or replace function mystify(int8) returns varchar as '
.....

chzeamt=> select mystify(3);
       mystify        
----------------------
  0000000000147097331
(1 row)

chzeamt=> select mystify(3);
       mystify        
----------------------
  0000000000147097331
(1 row)

chzeamt=> select mystify(8);
       mystify        
----------------------
  0000000000147097336
(1 row)

chzeamt=> select mystify(8);
       mystify        
----------------------
  0000000000147097336
(1 row)

chzeamt=> \q
zagy@lisa:~> psql -U chzeamt chzeamt
chzeamt=> select mystify(46578);
       mystify        
----------------------
  0000000000287181363
(1 row)

chzeamt=> select mystify(46578);
NOTICE:  Error occurred while executing PL/pgSQL function mystify
NOTICE:  line 15 at SQL statement
ERROR:  Relation 'mystifier' already exists

chzeamt=> create or replace function mystify(int8) returns varchar as '
.....

chzeamt=> select mystify(46570);
       mystify        
----------------------
  0000000000287181355
(1 row)

chzeamt=> select mystify(46570);
       mystify        
----------------------
  0000000000287181355
(1 row)

... and so on. The function might be stupid, but well... ;-)


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------