4 ms·

The bigger problem is views are tightly coupled to the underlying data.

by fny 4y ago

saltcured 4y ago
Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.
- gerdesj 4y ago
  Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
  - saltcured 4y ago
    The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.
    - 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 ...));

The bigger problem is views are tightly coupled to the underlying data.

by fny 4y ago

The bigger problem is views are tightly coupled to the underlying data.

Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.

gerdesj 4y ago
Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
- saltcured 4y ago
  The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.
  - 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 ...));