Implementing PostgreSQL User-Defined Table Functions in YugabyteDB

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:

create or replace function t_view(the_k in int)

  returns table(k int, v varchar)

  language sql

as $body$

  select t.k, t.v from t where t.k = t_view.the_k;

$body$;

Test it with:

xxxxxxxxxx

select k, v from t_view(2);

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:

xxxxxxxxxx

create table s as

select k, ('not '||v)::varchar(20) as v

from t;

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

xxxxxxxxxx

create function t_view(table_name in varchar, the_k in int)

  returns table(k int, v varchar)

  language plpgsql

as $body$

begin

  case table_name

    when 't' then

      return query

      select t.k, t.v from t where t.k = t_view.the_k;

    when 's' then

      return query

      select s.k, s.v from s where s.k = t_view.the_k;

  end case;

end;

$body$;

Test it with these two queries:

xxxxxxxxxx

select k, v from t_view('t', 2);

select k, v from t_view('s', 3);

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:

xxxxxxxxxx

structure of query does not match function result type

Returned type text does not match expected type character varying in column 2.

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.

xxxxxxxxxx

create or replace function t_view(table_name in varchar, the_k in int)

  returns table(k int, v varchar)

  language plpgsql

as $body$

begin

  return query execute

  'select t.k, t.v from "'||table_name||'" as t where t.k = $1'

  using t_view.the_k;

end;

$body$;

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:

xxxxxxxxxx

create table t(k int primary key, v varchar(20));

and this one:

xxxxxxxxxx

create table "t"(k int primary key, v varchar(20));

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:

xxxxxxxxxx

create function some_computed_rows()

  returns table(k t.k%type, v t.v%type)

  language plpgsql

as $body$

begin

  k := 7; v := 'seven';

  return next;

  k := 8; v := 'eight';

  return next;

  k := 9; v := 'nine';

  return next;

end;

$body$;

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

xxxxxxxxxx

insert into t(k, v)

select k, v from f();

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.