Postgres 函数总是返回一行 [英] Postgres function always return a row

问题描述

我在 Postgres 中编写了以下函数，但我遇到了一个问题:它总是返回一行.我的意思是，当没有用户匹配该对时，它会返回一行，所有列都为空.

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.

有没有办法让函数在没有结果时返回 0(零)行?

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;

有趣:如果我将返回类型从用户"更改为TABLE("Email" text,"GivenName" text,"ID" int8, "Password" text,"Salt" text)

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.

谢谢！

推荐答案

这是一个标量函数，所以每次都要返回值.你可能需要一个 SRF 函数，设置返回函数:

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)

这可能有点奇怪，因为PostgreSQL区分标量函数和SRF函数，但允许使用SRF作为标量和标量作为SRF(但最好在FROM子句中使用SRF函数，在查询表达式中使用标量函数:

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)

您可以看到，SRF 功能正在运行:

you can see, SRF function is working:

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

对于您的示例 - 只需将 return 子句更改为 RETURNS SETOF "User"

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

这篇关于Postgres 函数总是返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！