Обсуждение: 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