Re: Deleting temp table & relation does not exists

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';