Postgres function always return a row

Question

I wrote that following function in Postgres, but I've got a problem: It always returns a Row. I mean, when no user does match the pair it returns a row with all columns empty.

Are there a way to make a function return 0(zero) rows when there's no result?

CREATE OR REPLACE FUNCTION find_user_by_credentials(email text, password text)
RETURNS
"User"
AS
$$
    SELECT *
        FROM "User" AS "U"
    WHERE email = "U"."Email" AND "U"."Password" = md5(password || "U"."Salt")
    ;
$$
LANGUAGE SQL IMMUTABLE STRICT;

Interesting: If I change the return type from "User" to TABLE("Email" text,"GivenName" text,"ID" int8, "Password" text,"Salt" text)

It works as expected. But I really want use a "reference" to "User" because the maintance will be easier.

Thanks!

Answer 1

This is a scalar function, so it has to return value every time. You probably needs a SRF function, Set Returning Function:

CREATE OR REPLACE FUNCTION public.foo(boolean)
 RETURNS integer
 LANGUAGE sql
 AS $function$
  SELECT * FROM generate_series(1,2) WHERE $1;
 $function$

CREATE OR REPLACE FUNCTION public.srf_foo(boolean)
 RETURNS SETOF integer
 LANGUAGE sql
 AS $function$
  SELECT * FROM generate_series(1,2) WHERE $1;
 $function$

postgres=# \pset null [NULL]
Null display is "[NULL]".
postgres=# SELECT * FROM foo(false);
┌────────┐
│  foo   │
╞════════╡
│ [NULL] │
└────────┘
(1 row)

postgres=# SELECT * FROM srf_foo(false);
┌─────────┐
│ srf_foo │
╞═════════╡
└─────────┘
(0 rows)

This can be strange little bit, because PostgreSQL distingush between scalar functions and SRF functions, but allows to use SRF as scalar and scalar as SRF (but better to use SRF function inside FROM clause, and scalar function inside query expressions:

postgres=# SELECT srf_foo(false);
┌─────────┐
│ srf_foo │
╞═════════╡
└─────────┘
(0 rows)

postgres=# SELECT foo(false);
┌────────┐
│  foo   │
╞════════╡
│ [NULL] │
└────────┘
(1 row)

you can see, SRF function is working:

postgres=# SELECT * FROM srf_foo(true);
┌─────────┐
│ srf_foo │
╞═════════╡
│       1 │
│       2 │
└─────────┘
(2 rows)

For your example - just change return clause to RETURNS SETOF "User"

Answer 2

You need to change the returning type from "user" to SETOF "user". In case of singular rowtype, the function will always produce one row - with or without values, whereas use of SETOF will allow to return a collection, comprised of zero or multiple rows. I have also changed the function's language from SQL to PLPGSQL:

CREATE OR REPLACE FUNCTION find_user_by_credentials(email text, password text)
RETURNS SETOF "user" AS $$
DECLARE
  user_row RECORD;
BEGIN
  SELECT INTO user_row *
  FROM "user" AS "U"
  WHERE "U".email = "U"."email"
  AND "U"."password" = md5("U"."password" || "U"."salt");
  IF FOUND THEN
    RETURN NEXT user_row;
  END IF;
  RETURN;
END;
$$
LANGUAGE plpgsql VOLATILE;