August 4th, 2008 by depesz | Tags: , , , , , | 1 comment »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

Pavel Stehule wrote, and Tom Lane committed patch which ads new syntax for set-returning functions: “RETURNS TABLE".

Commit was on 18th of July, and the message was:

Implement SQL-spec RETURNS TABLE syntax for functions.
 
(Unlike the original submission, this patch treats TABLE output parameters
as being entirely equivalent to OUT parameters -- tgl)
 
Pavel Stehule

As you can tell from commit-log, new synatax doesn't give you any new functionality – it's just a syntactic sugar atop existing features.

But, I have to admit that it's a pretty tasty sugar:

CREATE FUNCTION test(max_i INT4) RETURNS TABLE (i INT4, double_i INT8) as $$
BEGIN
FOR i IN SELECT generate_series(1, max_i) LOOP
double_i := i * 2;
RETURN next;
END LOOP;
RETURN;
END;
$$ language plpgsql;

Of course the same could be done using “OUT" parameters:

CREATE FUNCTION test(IN max_i INT4, OUT i INT4, OUT double_i INT8) RETURNS SETOF RECORD as $$
BEGIN
FOR i IN SELECT generate_series(1, max_i) LOOP
double_i := i * 2;
RETURN next;
END LOOP;
RETURN;
END;
$$ language plpgsql;

but I like “RETURNS TABLE" better than out parameters. It's shorter and more readable. Good job.

  1. One comment

  2. # Mohammed Alhabshi
    Aug 4, 2008

    SQL by default was designed to be syntactic sugar syntax readable for non technical people. the INs and OUTs reminds of Assembly language where programmers should consider how,what and where data should be streamed in a computer. it is good to see more readable syntax. More, it will be better to see a default data type of the same input; where it is possible that the developer consider ” as data type in, as data type out”. The output-Data of a function is the same type of the data-in

Leave a comment