Re: slow SP with temporary tables, PLPGSQL problems

> Pavel Stehule <stehule@kix.fsv.cvut.cz> writes:
> > Can you help me other possibilities?
> 
> Create the temp table only once per connection (you can use ON COMMIT
> DELETE ROWS instead of ON COMMIT DROP to clean it out).  Then you won't
> need to use EXECUTE.
> 
I am not sure so it's possible. I use persistent connect via PHP. There 
isn't trigger for new connect. But I found same problem as you. PL/pgSQL 
can't    FOR r IN EXPLAIN SELECT ..  FOR r IN EXECUTE 'EXECUTE plan()'

I rewrite SP, and I have only one SELECT without two, 20% time less, but 
it's not readable code. I don't know how much work or if its possible move 
compilation time for PREPARE on every processing of this command. I think 
so its more natural for cmd PREPARE. But in this part of PL/pgSQL are more 
problems:

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF varchar AS $$
DECLARE r RECORD;
BEGIN PREPARE se(date) AS SELECT * FROM queue WHERE activated = $1; FOR r IN EXECUTE se(CURRENT_DATE) LOOP   RETUTRN
NEXTr.activated; END LOOP; DEALLOCATE se; RETURN;
 
END; $$ LANGUAGE plpgsql;

is this code correct? I think yes. But isn't true. I get message: function 
se(date) does not exist CONTEXT: SQL statement "SELECT se(CURRENT_DATE)".

Is only theory: FOR rn IN EXECUTE 'EXECUTE se(CURRENT_DATE)' LOOP
Now I get error: cannot open non-SELECT query as cursor. Prepared commands 
are good idea, but I cant use its now.

I have Pg 8.0.1

Regards
Pavel Stehule