Why do PostgreSQL functions return null columns instead of no rows when the return type is a table name?

Question

If I create a function f that queries a function I think it becomes easier to read if the return type is the name of a table instead of RETURNS TABLE(id integer, name text).

CREATE TABLE users ( id integer, name text );

CREATE OR REPLACE FUNCTION f() 
 RETURNS users 
 AS $$ 
   SELECT * FROM users
   WHERE FALSE 
 $$ 
 LANGUAGE SQL;

But I'm getting weird results when the query in the function is returning zero rows.

SELECT * FROM f();

Expected result

+------+--------+
| id   | name   |
|------+--------|
+------+--------+

Actual result

+--------+--------+
| id     | name   |
|--------+--------|
| <null> | <null> |
+--------+--------+

If there are more columns they will still all be null. If the query in the function returns any rows then it works as expected. I'm not getting this behaviour if I'm using the RETURNS TABLE(...) syntax.

Is there a way to get around this?

I'm using PostgreSQL 9.6

Laurenz Albe · Accepted Answer · 2020-02-14 08:28:00Z

10

That has nothing to do with the return type, but with the fact that you declared the function as returning a single row.

Replace

RETURNS users

with

RETURNS SETOF users

and your function will work as expected.

As your function is, it will return NULLs if the query has no results, and it will return the first row if the query has more than one result rows.

answered Feb 14, 2020 at 8:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why do PostgreSQL functions return null columns instead of no rows when the return type is a table name?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related