6

Given the schema

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    email varchar(254) NOT NULL
);
CREATE UNIQUE INDEX on users (lower(email));

CREATE FUNCTION all_users() RETURNS users AS $$
    SELECT * FROM users;
$$ LANGUAGE SQL STABLE;

, shouldn't SELECT * FROM all_users() (assuming the users table is empty) return no rows, not a row with all null values?

See the SQL Fiddle here: http://sqlfiddle.com/#!15/b5ba8/2

ma11hew28
  • 121,420
  • 116
  • 450
  • 651

2 Answers2

3

That's because your function is broken by design. It should be:

CREATE FUNCTION all_users() RETURNS SETOF users AS
'SELECT * FROM users' LANGUAGE sql STABLE;

Or alternatively, the more flexible form RETURNS TABLE (...) like @Clodoaldo posted. But it's generally wiser to use RETURNS SETOF users for a query with SELECT * FROM users.

Your original function always returns a single value (a composite type), it has been declared that way. It will break in a more spectacular fashion if you insert some rows.

Consider this SQL Fiddle demo.

For better understanding, your function call does the same as this plain SELECT query:

SELECT (SELECT u from users u).*;

Returns:

id     | email
-------+------
<NULL> | <NULL>

The difference: Plain SQL will raise an exception if the subquery returns more than one row, while a function will just return the first row and discard the rest.

As always, details in the manual.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you! I simplified my function for this question. The last line of my real function either inserts one row or no rows, like http://stackoverflow.com/a/12160777/242933. Seems I should still use `SETOF` for `INSERT RETURNING` because multiple rows can be returned if I insert multiple values (although I'm not) and "if the INSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted by the command." http://www.postgresql.org/docs/9.3/static/sql-insert.html – ma11hew28 Mar 28 '14 at 00:31
2

Your function returns records. So it must return at least one record. If you want an empty result set do return a table:

CREATE or replace FUNCTION all_users()
RETURNS table (id bigint, email varchar(254)) AS $$
    SELECT id, email FROM users;
$$ LANGUAGE SQL STABLE;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • The OP's function doesn't actually return "records", nor "at least one record". It returns a single composite type (or a "row type" if you will) - just as declared. Your link is exactly right. – Erwin Brandstetter Mar 27 '14 at 22:57
  • Also saying that is must return at least one record is a bit misleading - it must return exactly one row. If there are none - a row filled with nulls is returned. – Andronicus Feb 04 '21 at 13:58