Thread: Deleting temp table & relation does not exists

Hi all,

I am having a problem with a function. The function is called from a
trigger and functions correctly the first time it is called from a
certain connection. However, after that it fails during subsequent calls
from that connecting. New connections work for the first time as well,
and fail after that.
My hunch is that it has got something to do with the temp table that I
use to circumvent having to do a FOR .. IN EXECUTE ...
The error is "Error: relation ##### does not exist." The number is
always the same when using the same connection, but different when using
a different connection.

Code follows:

DROP FUNCTION fn_useradd();
CREATE FUNCTION fn_useradd() RETURNS OPAQUE AS '
DECLARE
     returncode INTEGER;
     usermgrstring VARCHAR;
     orgname VARCHAR;
     groepname VARCHAR;
BEGIN

     IF NEW.password IS NOT NULL THEN
         NEW.password := mycrypt(NEW.password);
         RAISE NOTICE ''[OLI] Password encrypted: %.'',NEW.username;
-- This notice is in the log so the error is after here
     END IF;

     EXECUTE ''create temp table varstore (store varchar(100))'';

         EXECUTE ''insert into varstore (store) select longname from
organisation where id = '' || New.orgid;
         select into orgname store from varstore;
         delete from varstore;

         EXECUTE ''insert into varstore (store) SELECT name FROM groep
WHERE id = '' || New.groepid;
         select into groepname store from varstore;
         delete from varstore;

     EXECUTE ''drop table varstore'';

     usermgrstring = groepname || ''\|'' || orgname || ''\|'' || NEW.qta;

     RAISE NOTICE ''[OLI] fn_usermgr() string: %'',usermgrstring;
-- This notice is not in the log so the error is before here

     select into returncode fn_usermgr(usermgrstring,'''');

     RETURN NEW;
END;
' LANGUAGE 'plpgsql';


It shouldn't really be a problem since this function will only really be
used from PHP which kills the connection at the end of each page, but I
would still like to know what is going on. Any ideas?

Jochem

On Thu, 21 Mar 2002, Jochem van Dieten wrote:
>
> I am having a problem with a function. The function is called from a
> trigger and functions correctly the first time it is called from a
> certain connection. However, after that it fails during subsequent calls
> from that connecting. New connections work for the first time as well,
> and fail after that.
> My hunch is that it has got something to do with the temp table that I
> use to circumvent having to do a FOR .. IN EXECUTE ...
> The error is "Error: relation ##### does not exist." The number is
> always the same when using the same connection, but different when using
> a different connection.
>
> Code follows:
>
> DROP FUNCTION fn_useradd();
> CREATE FUNCTION fn_useradd() RETURNS OPAQUE AS '
> DECLARE
>      returncode INTEGER;
>      usermgrstring VARCHAR;
>      orgname VARCHAR;
>      groepname VARCHAR;
> BEGIN
>
>      IF NEW.password IS NOT NULL THEN
>          NEW.password := mycrypt(NEW.password);
>          RAISE NOTICE ''[OLI] Password encrypted: %.'',NEW.username;
> -- This notice is in the log so the error is after here
>      END IF;
>
>      EXECUTE ''create temp table varstore (store varchar(100))'';
>
>          EXECUTE ''insert into varstore (store) select longname from
> organisation where id = '' || New.orgid;
>          select into orgname store from varstore;
>          delete from varstore;

** Shouldn't this be an execute as well since the table's identity
changes from execution to execution?

>
>          EXECUTE ''insert into varstore (store) SELECT name FROM groep
> WHERE id = '' || New.groepid;
>          select into groepname store from varstore;
>          delete from varstore;

** and this...

>
>      EXECUTE ''drop table varstore'';
>
>      usermgrstring = groepname || ''\|'' || orgname || ''\|'' || NEW.qta;
>
>      RAISE NOTICE ''[OLI] fn_usermgr() string: %'',usermgrstring;
> -- This notice is not in the log so the error is before here
>
>      select into returncode fn_usermgr(usermgrstring,'''');
>
>      RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';