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
