> 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