PostgreSQL：如何创建返回空表的函数或正确使用WHERE子句？

问题背景 #

已定义自定义类型：

CREATE TYPE p_store.MY_TYPE AS
(
    session_id               BIGINT,
    column2                  INTEGER
);

并创建返回该类型的函数：

CREATE OR REPLACE FUNCTION my_empty_type_creation_function() RETURNS MY_TYPE AS
$$
BEGIN
    RETURN NULL;
END
$$ LANGUAGE plpgsql IMMUTABLE
PARALLEL SAFE;

执行以下查询时抛出“column session_id does not exist”异常：

SELECT * FROM (select my_empty_type_creation_function() alias_1) WHERE alias_1.session_id is null;

解决方案 #

一、现有函数下正确编写WHERE子句

问题出在直接把复合类型的列当作普通表列访问字段，PostgreSQL需要用特定语法提取复合类型的内部字段：

• 方法1：括号包裹列名后访问
  用括号将复合类型列包裹，再通过.访问字段：

  SELECT * FROM (select my_empty_type_creation_function() alias_1) t WHERE (t.alias_1).session_id IS NULL;
  

  也可以在子查询里直接展开复合类型的所有字段：

  SELECT alias_1.* FROM (select my_empty_type_creation_function() alias_1) t WHERE (t.alias_1).session_id IS NULL;
  

• 方法2：用JSON提取操作符
  借助->操作符提取复合类型的字段（返回JSON类型），判断null的逻辑同样生效：

  SELECT * FROM (select my_empty_type_creation_function() alias_1) t WHERE alias_1->'session_id' IS NULL;
  

二、创建返回空表的函数

如果需求是返回空行集而非单个null的复合类型，可修改函数返回SETOF MY_TYPE，直接返回空结果：

• PL/pgSQL版本

  CREATE OR REPLACE FUNCTION my_empty_table_function() RETURNS SETOF p_store.MY_TYPE AS
  $$
  BEGIN
      RETURN; -- 直接返回空，不生成任何行
  END
  $$ LANGUAGE plpgsql IMMUTABLE
  PARALLEL SAFE;
  

• SQL版本（更简洁）

  CREATE OR REPLACE FUNCTION my_empty_table_function() RETURNS SETOF p_store.MY_TYPE AS
  $$
  SELECT * FROM p_store.MY_TYPE WHERE FALSE;
  $$ LANGUAGE sql IMMUTABLE
  PARALLEL SAFE;
  

调用时可直接当作表使用：

SELECT * FROM my_empty_table_function();

此时结果就是空表，也能直接添加WHERE子句（空表无匹配行）。

内容的提问来源于stack exchange，提问作者Bestows In Constructing