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;

Answer 1

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;