Thread: Transaction on start of session ?
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. 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. :( - I couldnt check is some temporary table exist inside the session. :( The way I could decide this problem is: - At each start of session some stored procedure must run (as some kind of transaction). And in this stored procedure I want to create all temporary tables that I want to use to store resulting rows from other stored procedures. And I shall not need to create any temporary table inside these procedures.
"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...