Deleting temp table & relation does not exists

Поиск
Список
Период
Сортировка
От Jochem van Dieten
Тема Deleting temp table & relation does not exists
Дата
Msg-id 3C9A481A.4020504@oli.tudelft.nl
обсуждение исходный текст
Ответы Re: Deleting temp table & relation does not exists  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "PG Explorer"
Дата:
Сообщение: Re: pg_hba.conf error
Следующее
От: Oliver Elphick
Дата:
Сообщение: [Fwd: Bug#139389: Unicode problems after update to 7.2]