这个PostgreSQL函数不应该返回零行吗? [英] Shouldn't this PostgreSQL function return zero rows?

问题描述

给出架构

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;

，SELECT * FROM all_users()(假设users表为空)是否不返回行，而不返回具有所有null值的行?

, shouldn't SELECT * FROM all_users() (assuming the users table is empty) return no rows, not a row with all null values?

在此处查看SQL提琴: http://sqlfiddle.com/#!15/b5ba8/2

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;

或者，更灵活的表格RETURNS TABLE (...) 像@Clodoaldo一样发布.但是使用RETURNS SETOF users进行SELECT * FROM users的查询通常更明智.

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.

请考虑以下 SQL Fiddle演示 .

Consider this SQL Fiddle demo.

为了更好地理解，您的函数调用与以下简单的SELECT查询相同:

For better understanding, your function call does the same as this plain SELECT query:

SELECT (SELECT u from users u).*;

返回:

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

差异:如果子查询返回多行，普通SQL将引发异常，而函数将仅返回第一行并丢弃其余行.

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.

与往常一样，手册中的详细信息.

这篇关于这个PostgreSQL函数不应该返回零行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！