4

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Billy
  • 2,406
  • 6
  • 28
  • 34
  • What exactly does the function do? –  Dec 14 '15 at 13:42
  • @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 Dec 14 '15 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? –  Dec 14 '15 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 Dec 14 '15 at 13:55
  • @a_horse_with_no_name I edited my question to include a Postgres example if that is of any help. – Billy Dec 14 '15 at 13:58
  • `@table` is an invalid identifier in SQL, that's why you have to rename it. I don't understand the purpose of the `update`. If you just write `select 1 union select 2 union select -1` you don't need the `update` at all. And still don't understand what the function is supposed to return. The rows from the select? Rows from a different table? Why isn't there an `insert` in your Postgres function? http://sqlfiddle.com/#!15/9bd10/1 –  Dec 14 '15 at 14:01
  • @a_horse_with_no_name this is a simple generic function explaining what I'm trying to do, not the actual function I'm trying to write. I need to know how to update the values that have already been inserted into the returning table. The first example is showing how this is done in MSSQL where `@table` is the correct syntax and the `return` at the end is required. Your SQL Fiddle is correct, but how would you alter the already inserted values? – Billy Dec 14 '15 at 14:14
  • Sorry, but your explanation does not make any sense to me. I think you need to explain the underlying problem you are trying to solve. Why would you want to insert and update? Why can't you just return the correct values with the select statement? –  Dec 14 '15 at 14:16

2 Answers2

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;
pozs
  • 34,608
  • 5
  • 57
  • 63
  • 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. – Billy Dec 14 '15 at 14:27
  • 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 ... – Erwin Brandstetter Dec 14 '15 at 14:49
  • 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`? – Billy Dec 14 '15 at 14:55
  • 1
    @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. – Erwin Brandstetter Dec 14 '15 at 15:01
  • @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. – pozs Dec 14 '15 at 15:48
  • Yes, probably so. The subquery is needless complication either way, though. – Erwin Brandstetter Dec 14 '15 at 16:01
2
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.

Related answers (among many others):

For more complex operations you could use a CTE

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

For even more sophisticated jobs, you could work with an actual temporary table:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228