{{announcement.body}}
{{announcement.title}}

Implementing PostgreSQL User-Defined Table Functions in YugabyteDB

DZone 's Guide to

Implementing PostgreSQL User-Defined Table Functions in YugabyteDB

In this article, we discuss how to implement PostgreSQL's User-Defined Table Functions in YugabyteDB.

· Database Zone ·
Free Resource


wooden-table-with-plant

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.

In part one, I gave a brief introduction to PostgreSQL’s table functions. Part three will cover some realistic use cases. I’ll introduce this second post by quoting that paragraph:

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.

Language SQL

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:

SQL


Test it with:

SQL


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:

SQL


Note: Notice the use of the explicit typecast. Without it, the column s.v would have the datatype, text.

SQL


Test it with these two queries:

SQL


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:

SQL


Note: Though varchar and 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.

SQL


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:

SQL


and this one:

SQL


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:

SQL


Of course, you can use it, as I’ve shown in the other examples:

SQL


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 for loop.

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: “43.6.1.2. 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.

Conclusion

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.


Further Reading

Spring Boot and PostgreSQL.

Fun With SQL: Window Functions in Postgres.

PostgreSQL’s Table-Valued Functions. 

Topics:
postgresql ,sql ,oracle ,kubernetes ,cloud native ,distributed database

Published at DZone with permission of Bryn Llewellyn . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}