Thread: Problems w/ Temp Tables

Thanks for your time.

 

I have an access/VBA system accessing data stored in a PostGRESQL db via functions.  One of the function uses temporary tables.  If I execute the function repeatedly via my vba code, I receive an error.  I found the following item on a PostGRESQL FAQ that describes my problem:

 

4.19) Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions?

PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that table is later dropped and recreated, and the function called again, the function will fail because the cached function contents still point to the old temporary table. The solution is to use EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

 

Can you tell me where should I implement the EXECUTE commands - in my function or with in my vba code?  My vba code typically uses - SELECT * from {function name}({parameters} to execute the postgresql function.

 

Thanks again.

---

View Athletes' Collections with Live Search. See it!

On Tuesday 23 January 2007 08:51, brian stapel <brians_224@hotmail.com>
wrote:
>
> Can you tell me where should I implement the EXECUTE commands - in my
> function or with in my vba code?  My vba code typically uses - SELECT
> * from {function name}({parameters} to execute the postgresql
> function.

You would use EXECUTE QUERY in place of all normal SQL statements that
access the table within your function.  The trickiest part of this is
proper quoting of user-supplied data.

However, one of my developers suggested a better way to handle this.
Use an exception block around creating the temporary table:

BEGIN
    TRUNCATE my_temp_table;
EXCEPTION
    WHEN undefined_table THEN
         CREATE TEMP TABLE my_temp_table (cols);
END;

.. and don't drop the table at the end of the function.  This allows
normal use of the table within the function, and will recreate it only
once per session.  You may also need to take more care in naming the
temporary table, since it will stick around in your session.

--
"Government big enough to supply everything you need is big enough to
take everything you have ... the course of history shows that as a
government grows, liberty decreases." -- Thomas Jefferson

am  Tue, dem 23.01.2007, um 10:51:51 -0600 mailte brian stapel folgendes:
> 4.19) Why do I get "relation with OID ##### does not exist" errors when
> accessing temporary tables in PL/PgSQL functions?
>
> PL/PgSQL caches function scripts, and an unfortunate side effect is that if a
> PL/PgSQL function accesses a temporary table, and that table is later dropped
> and recreated, and the function called again, the function will fail because
> the cached function contents still point to the old temporary table. The
> solution is to use EXECUTE for temporary table access in PL/PgSQL. This will
> cause the query to be reparsed every time.
>
>
>
> Can you tell me where should I implement the EXECUTE commands - in my function
> or with in my vba code?  My vba code typically uses - SELECT * from {function

in your function.

sql = 'SELECT * from ...';
EXECUTE sql;



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net