Accessing the Return Table in a Postgres Function

Question

In MSSQL when inside a multi-statement table valued function you can interact w/ the table as shown below.

CREATE FUNCTION dbo.test_func() RETURNS @table TABLE(id INT) AS
BEGIN
    INSERT INTO @table(id)
    SELECT 1 UNION SELECT 2 UNION SELECT 3

    UPDATE @table SET id = -1 WHERE id = 3
    RETURN
END
GO

Is there a way to accomplish this in Postgres 9.5? I'm not sure what to update as shown below.

CREATE FUNCTION test_func() RETURNS TABLE(id int) AS $$
BEGIN
    return QUERY SELECT 1 UNION SELECT 2 UNION SELECT 3;
    UPDATE ???? SET id = -1 WHERE id = 3;
END;
$$ LANGUAGE plpgsql STABLE;

@a_horse_with_no_name the function shown above is inserting 3 values into the returns table, it is then updating one of the inserted values, the one w/ the value of 3. Basically I need to know how to update data that has already been inserted into the returns table. I can post an example in Postgres if that would better clarify. — Billy
– Billy, Commented Dec 14, 2015 at 13:47
The insert and update can be written the same way when replacing the invalid @table identifier with a proper table name (the insert can actually be written a lot shorter using the values clause). But what does the single return do at the end? What does it return? — user330315
– user330315, Commented Dec 14, 2015 at 13:49
@a_horse_with_no_name it doesn't return anything, it is required in MSSQL when writing this type of function. I'm not sure what you mean by "can be written the same way when replacing the invalid @table identifier". What would I replace it with in Postgres? I know INSERT would turn into RETURN QUERY. — Billy
– Billy, Commented Dec 14, 2015 at 13:55
@a_horse_with_no_name I edited my question to include a Postgres example if that is of any help. — Billy
– Billy, Commented Dec 14, 2015 at 13:58

pozs · Accepted Answer · 2015-12-14 15:46:07Z

3

You cannot change a function's result set after it's sent with RETURN NEXT or RETURN QUERY.

But in PostgreSQL, you are not forced to send the whole result-set in a single statement (that's why, what you asking makes little sense in PostgreSQL). You can send rows to the result-set row-by-row with RETURN NEXT, you can send chunks of the result-set with RETURN QUERY/RETURN QUERY EXECUTE, or you can even mix that. (You can also exit from that function with a single RETURN without parameters).

So, probably what you want to do is something like:

CREATE FUNCTION test_func() RETURNS TABLE(id int) AS $$
BEGIN
    RETURN QUERY VALUES (1), (2);
    -- do some calculation
    RETURN NEXT -1;
END;
$$ LANGUAGE plpgsql STABLE;

If you really want to mimic what MSSQL does, you can use temporary tables, or (preferably) sub-selects inside functions:

CREATE FUNCTION test_func() RETURNS TABLE(id int) AS $$
BEGIN
    RETURN QUERY SELECT (SELECT CASE WHEN v = 3 THEN -1 ELSE v END res)
                 FROM   (VALUES (1), (2), (3)) v;
END;
$$ LANGUAGE plpgsql STABLE;

edited Dec 14, 2015 at 15:46

answered Dec 14, 2015 at 14:17

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Billy Over a year ago

I knew I could use a temp table but was trying to avoid that if possible as it is not needed in MSSQL. It sounds as if I cannot. Thank you for the clarification.

Erwin Brandstetter Over a year ago

The first query should be VALUES (1),(2) to return two rows with 1 column instead of 1 row with two columns - which wouldn't work in this context. And all of these can be IMMUTABLE, not just STABLE. And you don't need a subquery in the 2nd example. And it could be a simple SQL function ...

Billy Over a year ago

Is it possible to access the values that have already been inserted? In the first example you do RETURN QUERY for 1 and 2. Is there a way to get 1 and 2 back out like you can do in MSSQL by selecting from the @table?

Erwin Brandstetter Over a year ago

@Billy: No, not within the same function. The result set is out of reach once returned, just like pozs explained (there is no "insert" here). You could call the function from another function to alter result rows, but that's typically not necessary. Or you can work with an actual temporary table for more complex operations, created inside the function or outside.

pozs Over a year ago

@ErwinBrandstetter thanks for clarification. Yes, it was meant to be VALUES (1), (2). And I intentionally left the function's signature as in the question. I assumed, this is just a simplified version of a more complex function. If that's not the case, a simple VALUES(1),(2),(-1) would do the job without even defining a function.

|

Community · Accepted Answer · 2017-05-23 11:59:23Z

CREATE FUNCTION test_func()
  RETURNS TABLE(id int) AS
$func$
BEGIN
   RETURN QUERY
   SELECT CASE WHEN v = 3 THEN -1 ELSE v END
   FROM   (VALUES (1), (2), (3)) v;
END
$func$ LANGUAGE plpgsql IMMUTABLE;

Which does not require PL/pgSQL at all. A simple SQL function will do:

CREATE FUNCTION test_func()
  RETURNS SETOF int AS
$func$
   SELECT CASE WHEN v = 3 THEN -1 ELSE v END
   FROM   (VALUES (1), (2), (3)) v;
$func$ LANGUAGE sql IMMUTABLE;

Also demonstrating the simple form SETOF int instead of TABLE(id int) for the simple case. You can use either.

Collectives™ on Stack Overflow

Accessing the Return Table in a Postgres Function

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related