Implementing PostgreSQL User-Defined Table Functions in YugabyteDB

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. And 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 thus:

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.

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:

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.

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:

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:

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 datatypes. 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.

The function t_view() shows two distinct useful features. Firstly, it acts like a parameterized view—as already discussed. And secondly it encapsulates the choice between different source tables while still using static SQL to ensure proof against SQL injection. If there are too many source tables to choose between using a case statement, or if you don’t know the names until run-time, then you can use this dynamic SQL variant:

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:

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

and this one:

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 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:

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 like I’ve shown in the other examples:

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 its 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 as 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 criterion is met in all the use cases that I describe and implement in my third post—and there are very many such use cases.

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.

In my third post, “Four Compelling Use Cases for PostgreSQL Table Functions“, I discuss four specific, realistic use cases that call for user-defined table functions and explains how each case is met using complete, self-contained code examples.

What’s Next?

• Compare YugabyteDB in depth to databases like CockroachDB, Google Cloud Spanner and MongoDB.
• Get started with YugabyteDB on macOS, Linux, Docker, and Kubernetes.
• Contact us to learn more about licensing, pricing or to schedule a technical overview.