PostgreSQL’s Table-Valued Functions

Table-valued functions are an awesome thing. Many databases support them in one way or another and so does PostgreSQL. In PostgreSQL, (almost) everything is a table. For instance, we can write:

CREATE OR REPLACE FUNCTION 
    f_1 (v1 INTEGER, v2 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
END
$$ LANGUAGE plpgsql;

… and believe it or not, this is a table! We can write:

select * from f_1(1);

And the above will return:

+----+
| v2 |
+----+
|  1 |
+----+

It’s kind of intuitive if you think about it. We’re just pushing out a single record with a single column. If we wanted two columns, we could’ve written:

CREATE OR REPLACE FUNCTION 
    f_2 (v1 INTEGER, v2 OUT INTEGER, v3 OUT INTEGER)
AS $$
BEGIN
    v2 := v1;
    v3 := v1 + 1;
END
$$ LANGUAGE plpgsql;

… and then:

select * from f_2(1);

And the above will return:

+----+----+
| v2 | v3 |
+----+----+
|  1 |  2 |
+----+----+

That’s useful, but those are just single records. What if we wanted to produce a whole table? It’s easy, just change your functions to actually return TABLE types, instead of using OUT parameters:

CREATE OR REPLACE FUNCTION f_3 (v1 INTEGER)
RETURNS TABLE(v2 INTEGER, v3 INTEGER)
AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM (
        VALUES(v1, v1 + 1), 
              (v1 * 2, (v1 + 1) * 2)
    ) t(a, b);
END
$$ LANGUAGE plpgsql;

When selecting from the above very useful function, we’ll get a table like so:

select * from f_3(1);

And the above will return:

+----+----+
| v2 | v3 |
+----+----+
|  1 |  2 |
|  2 |  4 |
+----+----+

And we can LATERAL join that function to other tables if we want:

select *
from book, lateral f_3(book.id)

… which might yield, for example:

+----+--------------+----+----+
| id | title        | v2 | v3 |
+----+--------------+----+----+
|  1 | 1984         |  1 |  2 |
|  1 | 1984         |  2 |  4 |
|  2 | Animal Farm  |  2 |  4 |
|  2 | Animal Farm  |  4 |  6 |
+----+--------------+----+----+

In fact, it appears that the keyword LATERAL is optional in this case, at least for PostgreSQL.

Table-valued functions are very powerful!

Discovering table-valued functions

From jOOQ’s schema reverse-engineering perspective, things might get a bit tricky as can be seen in this Stack Overflow question. PostgreSQL deals with OUT parameters in a very similar way as with TABLE return types. This can be seen in the following query against the INFORMATION_SCHEMA:

SELECT r.routine_name, r.data_type, p.parameter_name, p.data_type
FROM   information_schema.routines r
JOIN   information_schema.parameters p
USING (specific_catalog, specific_schema, specific_name);

… and the output:

routine_name | data_type | parameter_name | data_type
-------------+-----------+----------------+----------
f_1          | integer   | v1             | integer
f_1          | integer   | v2             | integer
f_2          | record    | v1             | integer
f_2          | record    | v2             | integer
f_2          | record    | v3             | integer
f_3          | record    | v1             | integer
f_3          | record    | v2             | integer
f_3          | record    | v3             | integer

As you can see, the output is really indistinguishable from that perspective. Luckily, we can also join the pg_catalog.pg_proc table, which contains the relevant flag to indicate if a function returns a set or not:

SELECT   r.routine_name, 
         r.data_type, 
         p.parameter_name, 
         p.data_type, 
         pg_p.proretset
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
ORDER BY routine_name, parameter_name;

Now, we’re getting:

routine_name | data_type | parameter_name | data_type | proretset
-------------+-----------+----------------+-----------+----------
f_1          | integer   | v1             | integer   | f
f_1          | integer   | v2             | integer   | f
f_2          | record    | v1             | integer   | f
f_2          | record    | v2             | integer   | f
f_2          | record    | v3             | integer   | f
f_3          | record    | v1             | integer   | t
f_3          | record    | v2             | integer   | t
f_3          | record    | v3             | integer   | t

We can see that f_3 is the only function actually returning a set of record, unlike f_1 and f_2, which only return a single record.

Now, remove all those parameters that are not OUT parameters, and you have your table type:

SELECT   r.routine_name, 
         p.parameter_name,
         p.data_type,
         row_number() OVER (
           PARTITION BY r.specific_name 
           ORDER BY p.ordinal_position
         ) AS ordinal_position
FROM     information_schema.routines r
JOIN     information_schema.parameters p
USING   (specific_catalog, specific_schema, specific_name)
JOIN     pg_namespace pg_n
ON       r.specific_schema = pg_n.nspname
JOIN     pg_proc pg_p
ON       pg_p.pronamespace = pg_n.oid
AND      pg_p.proname = r.routine_name
WHERE    pg_p.proretset
AND      p.parameter_mode = 'OUT'
ORDER BY routine_name, parameter_name;

Which will give us:

routine_name | parameter_name | data_type | position |
-------------+----------------+-----------+----------+
f_3          | v2             | integer   |        1 |
f_3          | v3             | integer   |        2 |

How to run such queries in jOOQ?

Once the above code is generated, you can easily call the table-valued function in any jOOQ query. Consider again the BOOK example (in SQL):

select *
from book, lateral f_3(book.id)

… and with jOOQ:

DSL.using(configuration)
   .select()
   .from(BOOK, lateral(F_3.call(BOOK.ID)))
   .fetch();

The returned records then contain values for:

record.getValue(F_3.V2);
record.getValue(F_3.V3);

All that typesafety is only available in the upcoming jOOQ 3.5, for free! (SQL Server, Oracle, and HSQLDB table-valued functions are already supported!)

jOOQ is the best way to write SQL in Java

jOOQ Newsletter: February 26, 2014

Subscribe to the newsletter here

Tweet of the Day

Our followers, users and customers are shouting their love for jOOQ to the world. Here are:

Ben Hood who is constantly discovering new useful features in jOOQ.

Antoine Comte who is skipping MyBatis to jump directly to jOOQ (in French)

Both guys are absolutely right, of course!

jOOQ 3.4 Outlook

jOOQ 3.3 was an exciting release. But now we’re heading towards the next upgrade, and it’s not going to be any less exciting. Here are the top interesting topics that might be in scope for jOOQ 3.4:

  • IBM Informix support for the jOOQ Professional Edition.
  • Typesafe DDL support for the most common statement types.
  • Transaction management. We’ve been discussing this a lot of times and we would love to provide some jOOQ-style Java-8 ready default behaviour. As always, with plenty of options to override the defaults.
  • TABLE types and TABLE-valued functions in all databases. This is already very useful in jOOQ 3.3’s SQL Server integration.
  • SQL 2 jOOQ improvements and a UI to transform SQL to jOOQ code.

As you can see, plenty of goodies coming up! Missing a feature? Write us!

Community work

We’re thrilled to have spotted yet another great article by Vlad Mihalcea, who is discovering the merits of using jOOQ for interaction with stored procedures and functions. If you’re doing a deep SQL integration with your favourite database of choice, stored procedures are a very useful tool in your tool chain. Unfortunately, neither JDBC nor JPA offer any convenience in that area, in a way as jOOQ does.Read the full article here.

Besides, we’re very happy to have found that our friends at RebelLabs are excited with our blog posts, such that they have featured a guest post of ours on their blog. RebelLabs have a tremendous amount of great content from many writers, so we’re very honoured to present our guest post to you.

SQL Zone – 60% of SQL Developers Fail

One of the most important topics when writing SQL is performance, as the database is the bottleneck in most applications. And surprisingly, 60% of all SQL developers will fail this little test performed by the Use The Index, Luke guys.

This blog post is a must-read for all juniors and seniors alike. You never stop learning about correct SQL indexing.

SQL Zone – JDBC and Booleans

Boolean types have been introduced only late in the SQL standard, namely inSQL:1999. Even today, not all databases natively support BOOLEAN or BIT types. Most importantly, we can still wait for them in Oracle for a while.

What does this mean for Java developers striving for a maximum compatibility through JDBC? Read our findings in this blog post here.

Upcoming Events

February has been a bit quiet as we were preparing for the jOOQ 3.3 release, but soon you’ll get another chance at hearing about jOOQ or SQL in general in any of these upcoming events:

With our new SQL-talk, we’ll no longer just spread some jOOQ love, but also some SQL love in general. We believe that SQL deserves more presence in today’s software engineering talks, and who would be better to talk about SQL than us? Are you interested in hosting this talk at your company? Contact us!

Stay informed about 2014 events on www.jooq.org/news.