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
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.
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." postgresql.org/docs/9.3/static/sql-insert.htmlYour 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;
