Re: slow SP with temporary tables, PLPGSQL problems

> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)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 NEXT r.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