Re: Transaction on start of session ?

"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...