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.
It is true that postgresql does not have an easy way to return multiple rows
from a function, but it can be done with some typing.
(select MyStartfn(...), Myfn('name1') as name1, Myfn('name2') as name2) as
ttable
The idea is that you write a function "MyStartfbn(...)" which does the
operation. The function "Myfn(...)" accepts a field name, or some kind of
marker, to return the rows.
There are a number of strategies on how to do this, but you kind of need to
understand how to write PostgreSQL functions.
Paul wrote:
Show quoted text
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.---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)