Should this PostgreSQL function return null rows?

Question

Given the scheme

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 table is usersempty) not return a row, not a row with all values null?

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

Answer 1

This is because your function is disrupted by design. It should be:

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

Or, alternatively, a more flexible form RETURNS TABLE (...) as @Clodoaldo posted . But it’s usually wiser to use RETURNS SETOF usersto query with SELECT * FROM users.

( ), . , .

SQL Fiddle.

, SELECT:

SELECT (SELECT u from users u).*;

:

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

: Plain SQL , , .

, .

Answer 2

. . , :

CREATE or replace FUNCTION all_users()
RETURNS table (id bigint, email varchar(254)) AS $$
    SELECT id, email FROM users;
$$ LANGUAGE SQL STABLE;