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