3 ms·

>functions that take sets as input and produce arbitrarily different sets as output It is possible to return arbitrary sets from functions in many systems-- re

by oedo 4y ago

>functions that take sets as input and produce arbitrarily different sets as output It is possible to return arbitrary sets from functions in many systems-- recent versions of the SQL standard even specify table functions (with syntax "RETURNS TABLE(columns)")[0] as well as polymorphic table functions (PTFs)[1]. And in addition to implementations of those standards, extensions like PL/[pg]SQL also have a few other ways of effecting table-like returns (parameter modes, composite type returns). >function overloading Not standard, but some systems support it[2]. [0] https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC... [1] https://share.ansi.org/Shared%20Documents/News%20and%20Publications/Links%20Within%20Stories/SQL%20standard%20published_POST.pdf https://share.ansi.org/Shared%20Documents/News%20and%20Publi... [2] https://www.postgresql.org/docs/current/xfunc-overload.html https://www.postgresql.org/docs/current/xfunc-overload.html

saltcured 4y ago
Right, I'm aware of table-returning functions and the general idea of functions as table sources in a FROM clause. I think the biggest gap is not having complementary table-consuming function signatures and ways to use functions as table sinks other than aggregate function calls. I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal: CREATE OR REPLACE FUNCTION -- invent a table-sink func signature? func1( TABLE t1(a int, b text, c text) ) RETURNS TABLE (x int, y boolean) AS $$ -- access input set via declared name SELECT a, b < c FROM t1 $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION func2( TABLE t1(a int, b boolean) ) RETURNS int AS $$ SELECT a WHERE b $$ LANGUAGE SQL; -- implicit positional matching of func input cols? SELECT f1r.x, f1r.y FROM func1( SELECT e1, e2, e3 FROM ...) AS f1r; -- explicit input mapping to override positions? SELECT f1r.x, f1r.y FROM func1( SELECT e1, e2, e3 INTO b, a, c FROM ...) AS f1r; -- function composition SELECT func2(func1( SELECT e1, e2, e3 FROM ...));

by oedo 4y ago

saltcured 4y ago

Right, I'm aware of table-returning functions and the general idea of functions as table sources in a FROM clause. I think the biggest gap is not having complementary table-consuming function signatures and ways to use functions as table sinks other than aggregate function calls. I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal: CREATE OR REPLACE FUNCTION -- invent a table-sink func signature? func1( TABLE t1(a int, b text, c text) ) RETURNS TABLE (x int, y boolean) AS $$ -- access input set via declared name SELECT a, b < c FROM t1 $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION func2( TABLE t1(a int, b boolean) ) RETURNS int AS $$ SELECT a WHERE b $$ LANGUAGE SQL; -- implicit positional matching of func input cols? SELECT f1r.x, f1r.y FROM func1( SELECT e1, e2, e3 FROM ...) AS f1r; -- explicit input mapping to override positions? SELECT f1r.x, f1r.y FROM func1( SELECT e1, e2, e3 INTO b, a, c FROM ...) AS f1r; -- function composition SELECT func2(func1( SELECT e1, e2, e3 FROM ...));