We only do the connection "refesh" in the Lightning Admin Query editorfor testing our SQL that uses temp tables.
refreshing the connection eliminates the OID does not exist problems.
We put everything into stored procs and use them from Delphi applications and still never use execute in
our procs.
I think the key is we use ON COMMIT DELETE ROWS when we create our temp tables, we don't ever drop them
until the client disconnects. using the ON COMMIT DELETE ROWS just re uses the the same temp table over
and over again. You only need to use select if you drop the temp table and recreate it multiple times in
the same session.
Here is a example of how we use temp tables:
(NOTE: I ADDED the temp table to this function, you don't actually need it in this case becuase
the cursor can just be refereneced against the select statement)
This can be called over and over again from the same connection because the temp table is not dropped, it's
just re-used. when the client does disconnect it gets dropped.
You could also truncate the temp table at the end of the function if you wanted, then you don't have
data sitting in the table until the next function call.
CREATE or REPLACE FUNCTION admin.spadm_get_status_list()
RETURNS pg_catalog.refcursor AS
$BODY$
DECLARE
return_cursor refcursor;
BEGIN
return_cursor = 'return_cursor';
IF iftableexists('temp_get_status_list') THEN
RAISE NOTICE 'temp table already exists';
ELSE
CREATE TEMP TABLE temp_get_status_list
(
STATUS_ID SMALLINT,
DESCRIPTION VARCHAR(50))WITHOUT OIDS ON COMMIT DELETE ROWS;
END IF;
INSERT INTO temp_get_status_list
(
STATUS_ID, DESCRIPTION
)
SELECT status_id, description
FROM admin.admin_status
ORDER BY 1;
OPEN return_cursor FOR SELECT * FROM temp_get_status_list;
RETURN return_cursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
> If you refresh the connection each time you run a query, maybe you
> don't need to use EXECUTE with temporary tables in stored procedures,
> but who does that in a production database application? Most people want
> to re-use connections for performance reasons.
>
> Dennis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
