Re: Error OID

Yohanes Purnomo a écrit :
> [...]
> I create a function:
>
> CREATE OR REPLACE FUNCTION ReProses()
> RETURNS BOOLEAN
> AS $$
>
> DECLARE
> nHasil Numeric;
>
> BEGIN
> CREATE TEMP TABLE tmpTtlRekening WITHOUT OIDS ON COMMIT DROP
> AS
> SELECT Rekening, SUM(Debet) AS Debet, SUM(Kredit) AS Kredit
> FROM Jurnal
> GROUP BY Rekening;
>
> SELECT COALESCE(SUM(Debet - Kredit), 0)
> INTO nHasil
> FROM tmpTtlRekening;
>
> -- bla,bla
> RETURN '1';
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> Execute Query:
>
> SELECT ReProses()
>
> Result is fine, but when i execute again
>

When you run a function for the first time in the session, PostgreSQL
keeps in cache some information... query plans for example.

> SELECT ReProses()
>
> ERROR: relation with OID 41573 does not exist
>

When you run it a second time, it uses the query plan in cache. As
tmpTtlRekening has been drop at the end of the first run, its OID will
change but the old OID is still in cache. So PostgreSQL tries to get
information from the old temp table.

If you don't want that PostgreSQL put the query plan in cache, use the
EXECUTE statement.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com