Re: Transaction on start of session ?

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:
>
> 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(at)postgresql(dot)org)