主页 / dba / 问题 / 317489
Accepted
Biller Builder
Asked: 2022-09-28 09:00:57 +0800 CST

如何在 SQL 函数的复合类型参数上运行“SELECT”？

  • 772

示例：https ://dbfiddle.uk/bCSwVpd9

数据库：

CREATE TABLE entities (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TYPE entity_id AS (
  id bigint
);

功能：

CREATE FUNCTION get_entities (
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  entity_ids entity_id DEFAULT NULL
)
RETURNS TABLE (
  id bigint
)
LANGUAGE SQL
AS $BODY$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      -- filter by id list if provided
      entity_ids IS NULL OR id IN (
        SELECT
          id
        FROM
          entity_ids
      )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$BODY$;

关键是我想编写一个分页多选函数，它可以从分页信息和一组 ID 中工作。它上面的函数的问题崩溃了：

ERROR:  relation "entity_ids" does not exist
LINE 22:           entity_ids

对这个问题也有类似的反应：first，second。但是，它们围绕作为标识符字符串的参数，而不是复合记录类型和 use plpgsql，这可能不重要，也可能不重要。

postgresql functions

1 个回答

  1. Best Answer

    问题标题和问题正文提出了不同的问题，因此我将同时解决这两个问题。

    至于问题标题：您不需要SELECT针对复合类型，您可以使用点符号直接引用其中的字段。例如，如果您的类型定义为

    CREATE TYPE entity_id AS (
  id bigint,
  name text
);

    并且该类型的实例被传递给带有名为 的参数的函数foo，您可以分别将字段引用为foo.id和foo.text。

    至于问题主体：您不能将记录集作为函数参数传递，但您可以将其转换为数组并传递它。在您的情况下，使用数组作为id列表参数，并使用= ANY运算符来搜索它：

    CREATE OR REPLACE FUNCTION get_entities(pagination_limit bigint DEFAULT 25, pagination_offset bigint DEFAULT 0, entity_ids bigint[] DEFAULT NULL)
 RETURNS TABLE(id bigint)
 LANGUAGE sql
AS $function$
  WITH input_entities AS (
    SELECT
      id
    FROM
      entities
    WHERE
      entity_ids IS NULL OR id = ANY ( entity_ids )
    ORDER BY
      id ASC
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id
  FROM
    input_entities
  ORDER BY
    id
$function$

    像这样调用：

    SELECT * FROM get_entities(25,0,ARRAY[1,2,3,4,5]::bigint[]);

    或者要使用子查询的输出，请使用ARRAY()构造函数：

    SELECT * FROM get_entities(25,0,ARRAY(SELECT ids FROM mytable ...));

    由于您想了解如何将记录集从表值函数传递到另一个：

    SELECT * FROM get_entities(25,0,ARRAY(SELECT id FROM myfunc());

    当然，如果myfunc()返回一个数组，构造函数是不必要的。

    上面表达的两个想法可以结合起来；你可以有一个复合类型实例的数组。

    参考：https ://www.postgresql.org/docs/14/arrays.html

    • 1

相关问题