I have a function with pl/pgSQL such as...
CREATE FUNCTION comp_userExp (TEXT) RETURNS INTEGER AS '
DECLAREstUserName TEXT;
BEGINstUserName := upper($1);
CREATE TEMP TABLE comuser AS SELECT * FROM comt_user1 WHERE userName=stUserName;
CREATE TEMP TABLE comUser1 AS SELECT a.userName FROM comt_user2 a, comuser b WHERE a.userName = b.userName ORDER BY
b.userName;
CREATE TEMP TABLE comUser2 AS SELECT a.userName FROM comt_user3 a, comuser b WHERE a.userName = b.userName ORDER BY
b.userName;
DROP Table comuser,comuser1,comuser2;
RETURN 0;
END;' LANGUAGE 'PLPGSQL';
This function can't run twice in the same connection session. After tracing
error, the reason is because 2nd and 3rd SQL refer to table comuser. It will
cause "can't find relation number xxxxx". I think ,after first run, the
procedure just use relation number to access table, while actually table was
dropped and create again with a different relation number on the 2nd time.
If I disconnect database, and re-connect again, it won't cuase any problem.
If don't want to disconnect and connect, is there any way to fix the
problem?
JACK
JACK
