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!

Pavel Stehule · Accepted Answer · 2015-05-10 14:59:55Z

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"

percy · Accepted Answer · 2015-05-10 15:16:21Z

4

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;

answered May 10, 2015 at 15:16

percy

1,12811 silver badges9 bronze badges

1 Comment

Pavel Stehule Over a year ago

Just note - from performance perspective is not good to use plpgsql for these trivial functionality. SQL can be usually significantly faster due inlining to outer query. Other note - if you have to use plpgsql - then you can reduce your example to one statement - `RETURN QUERY ...

Collectives™ on Stack Overflow

Postgres function always return a row

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related