Implementing PostgreSQL User-Defined Table Functions in YugabyteDB
Join the DZone community and get the full member experience.Join For Free
Welcome part two of a three-part series of posts on PostgreSQL’s table functions. These functions can be easily leveraged in a distributed SQL database like YugabyteDB, which is PostgreSQL compatible.
A regular language plpgsql user-defined function is implemented using the plain return statement. Its argument can only be a single value. A user-defined table function, in contrast, is implemented using either return next, to return the next explicitly computed row, or return query, to return the entire result set of a select statement. You can define the statement using either static SQL or dynamic SQL. You can also implement a language sql table function by defining the body entirely with a single static SQL select statement.
A view is defined by a single
select statement — and a language SQL user-defined table function is also defined by a single select statement. The critical difference is that the table function, like all kinds of functions, is typically defined with formal parameters. You can use one of these at a syntax spot in the defining
select statement where a value is needed. In other words, you have a parameterized view. Here’s an example:
Test it with:
As long as you know the identity of the table (or tables) over which you want a parameterized view, then this kind of table function is the perfect fit. The meaning of the code couldn’t be more transparent. I show the usefulness of such a language SQL table function in the “Parameterized view” use case in my third post.
You may also like: Spring Boot and PostgreSQL
Language Plpgsql With Return Query
The simplest example has just a single return query followed by a select statement. But if this functionality is sufficient for you, you can use a language SQL table function. Here’s a more interesting example. It relies on two tables: table,
t, as used above and table s with the same row shape. Here’s a convenient way to create it:
Note: Notice the use of the explicit typecast. Without it, the column
s.v would have the datatype,
Test it with these two queries:
Note: Without the typecast in the
create table as select statement for table
s, the query in the second leg of the
case statement would cause this error:
text have the same functionality, they are formally different data types. This is a feature of PostgreSQL, and therefore of YugabyteDB. It brings opportunities for programmer errors. But, you have no choice but to understand it and program accordingly.
Test it with the same queries that you used to test the static SQL variant. You’ll see that both variants produce the same results.
Notice that one of the function’s formal parameters is the source table’s
name. I convert this, following standard good practice, into an injection-proof SQL identifier by surrounding it with double quotes. I’m relying on the fact that this
create table statement:
and this one:
have the same effect. Any reference to the table, whose name is
lower-case-t, can be made either with the unquoted identifier
t, taken case-insensitively to denote the lower-case name, or the double quoted identifier
“t”, taken case-sensitively.
Note: this rule is the exact opposite from the Oracle Database rule. There, too, unquoted identifiers are taken case-insensitively; but there, they’re taken to denote the upper-case name!
Language Plpgsql With Return Next
This flavor of the table function is the most general. In the limit, you can compute the set of values for each returned row explicitly. Here’s a trivial example to show the syntax:
Of course, you can use it, as I’ve shown in the other examples:
In more typical uses, you can’t express what you want using just a single
select statement but, rather, you assemble the returned results from one or more queries, using some procedural logic to combine these. As an exercise, you could replicate the behavior of a single
select statement that uses a window function by implementing a table function that establishes the window of interest explicitly within the body of a
Of course, you should always use pure SQL when this is possible. But, there’s a class of requirements where you can’t do this. The canonical example in this class is provided by a stock price history table — in other words, a graph of price against time — where you want to find a so-called “W” pattern (a local minimum followed by a local maximum followed by another local minimum, all within a defined time interval).
I use a language plpgsql with
return next table function, whose return table has just a single,
varchar column, in the “Pretty printed ad hoc reports for administrators” use case described in my third post. There, many of the rows (like headings and rule-offs) are produced, each with their own dedicated
return next statement. But, others are computed in a
cursor for loop to embed query results in the report.
PostgreSQL Table Functions Are not Pipelined
YugabyteDB currently uses the PostgreSQL 11.2 source code. Look in the docs for this version for this section: “184.108.40.206. RETURN NEXT and RETURN QUERY”. And look for the note that starts thus:
The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function…
The same note is found in the PostgreSQL docs for Version 12.1.
This means that a table function doesn’t behave a row source, as does a regular permanent, temporary table, or view, delivering rows only as they’re needed. Rather, the entire result set that the table function defines is materialized before any of its rows can be consumed. If the result set is huge, this can imply spilling to disc — bringing corresponding performance problems.
This stands in contrast to the behavior of Oracle database’s so-called pipelined table functions, where the results are delivered on-demand, row-source style.
The consequence of this current behavior is that PostgreSQL table functions are most useful when they deliver small, bounded result sets.
This post has explained how to implement user-defined table functions, in all variants. But it used only bare-bones examples that indicate generalized classes of use-case.
Published at DZone with permission of Bryn Llewellyn. See the original article here.
Opinions expressed by DZone contributors are their own.