"Paul" <magamos@mail.ru> writes:
> The problem:
>
> I want to create a function that returns the result as many tuples (many
> rows of records). Unlike MSSQL, Oracle, etc PostgreSQL couldnt do it. So, I
> decided the only way to do it is to return result data into temporary table.
In 7.2 you can also return an open cursor, which should work for your
application.
> But:
>
> - If I create table into stored procedure, I got the error from the second
> call of this procedure inside the same session. It's because Plpgsql makes
> precompilation of the query at the first call of this procedure inside the
> session. And when I delete the result temporary table that this procedure
> returned me and call this procedure second time, the query with "INSERT"
> (that is already precompiled) uses the table that was already deleted, but
> not the table that was just created. :(
Use the EXECUTE statement in pl/pgsql to build a dynamic query that
isn't cached. This will work in 7.1 or 7.2.
-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/
Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...