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.

Search Discussions

  • Mark L. Woodward at Mar 13, 2002 at 11:01 pm
    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@postgresql.org)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 11, '02 at 12:05p
activeMar 13, '02 at 11:01p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Paul: 1 post Mark L. Woodward: 1 post

People

Translate

site design / logo © 2022 Grokbase