CODEWITHSUNDEEP
sql-serverpostgresqlfunctionplpgsql
Billy
Billy

Reputation: 2436

Accessing the Return Table in a Postgres Function

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;

Upvotes: 4

Views: 5063

Answers (2)

pozs
pozs

Reputation: 36234

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;

Upvotes: 3

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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:

Upvotes: 2

Related Questions