将标签数组传递给plpgsql函数，并在WHERE条件下使用 [英] Pass array of tags to a plpgsql function and use it in WHERE condition

问题描述

我想创建一个基于其tags返回items的函数.但是，我不知道如何在IN()子句中格式化数组.我相信这就是为什么我没有结果的原因.

I'd like to create a function that returns items based on their tags. However, I do not know how to format an array in the IN() clause. I believe that is why I get no result.

这就是我得到的:

CREATE OR REPLACE FUNCTION getItemsByTag(tags text[])
  RETURNS TABLE (id bigint, title text, tag text[]) AS $$
BEGIN
    IF array_length(tags, 1) > 0

    THEN
            EXECUTE format('
            SELECT d.id, d.title, array_agg(t.title)
            FROM items d
            INNER JOIN item_tags dt
            ON dt.item_id = d.id
            INNER JOIN tags t
            ON t.id = dt.tag_id
            AND t.title IN (%L)
            GROUP BY d.id, d.title
            ', array_to_string(tags, ','));
    -- ELSE ...
    END IF;

END;
$$ LANGUAGE plpgsql;

然后我打电话:

select getItemsByTag('{"gaming", "sport"}');

即使有标有游戏"的物品，我也没有得到结果.

I get no result even though there are items tagged with "gaming".

CREATE TABLE items(
id serial primary key,
title text);

CREATE TABLE tags(
id serial primary key,
title text);

CREATE TABLE item_tags(
item_id int references items(id),
tag_id int references tags(id),
primary key(item_id, tag_id));

insert into items (title) values ('my title 1'), ('my title 2');
insert into tags (title) values ('tag1'), ('tag2');
insert into item_tags (item_id, tag_id) values (1,1), (1, 2);

功能:

CREATE OR REPLACE FUNCTION getItemsByTag(tags text[])
  RETURNS TABLE (id bigint, title text, tag text[]) AS $$
BEGIN

    IF array_length(tags, 1) > 0

    THEN
            EXECUTE format('
            SELECT d.id, d.title, array_agg(t.title)
            FROM items d
            INNER JOIN item_tags dt
            ON dt.item_id = d.id
            INNER JOIN tags t
            ON t.id = dt.tag_id
            AND t.title IN (%L)
            GROUP BY d.id, d.title
            ', array_to_string(tags, ','));
    -- ELSE ...
    END IF;
END;
$$ LANGUAGE plpgsql;

致电:

 select getItemsByTag('{"tag1", "tag2"}');

推荐答案

您实际上不是 返回 结果.您可以使用RETURN QUERY EXECUTE来实现.示例:

You are not actually returning the result. You would use RETURN QUERY EXECUTE for that. Example:

• PostgreSQL参数化的表函数中的按/限制"

但是您不需要在这里以...开头的动态SQL.

But you don't need dynamic SQL here to begin with ...

CREATE OR REPLACE FUNCTION get_items_by_tag(VARIADIC tags text[])
  RETURNS TABLE (id int, title text, tag text[]) AS
$func$
BEGIN
   IF array_length(tags, 1) > 0 THEN
      -- NO need for EXECUTE
      RETURN QUERY
      SELECT d.id, d.title, array_agg(t.title)
      FROM   items d
      JOIN   item_tags dt ON dt.item_id = d.id
      JOIN   tags t       ON t.id = dt.tag_id
      AND    t.title = ANY ($1)     -- use ANY construct
      GROUP  BY d.id;               -- PK covers whole table
      -- array_to_string(tags, ',') -- no need to convert array with ANY
-- ELSE ...
   END IF;
END
$func$  LANGUAGE plpgsql;

使用实际数组调用:

SELECT * FROM get_items_by_tag(VARIADIC '{tag1,tag2}'::text[]);

或致电带有项目列表(字典")的

Or call with list of items ("dictionary"):

SELECT * FROM get_items_by_tag('tag1', 'tag2');

要点

• 使用RETURN QUERY实际返回结果行.

  Major points

  • Use RETURN QUERY to actually return resulting rows.

    • PostgreSQL函数返回多个结果集

    除非需要，否则请不要使用动态SQL. (这里没有EXECUTE.)

    Don't use dynamic SQL unless you need it. (No EXECUTE here.)

    使用ANY构造代替IN.为什么?

    Use an ANY construct instead of IN. Why?

    • 如何在Rails的WHERE子句中使用ANY而不是IN?

    为方便起见，我建议使用VARIADIC函数.这样，您可以选择传递数组或项目列表.参见:

    I suggest a VARIADIC function for convenience. This way you can either pass an array or a list of items at your choosing. See:

    • 在单个参数中传递多个值

    尽可能避免在Postgres中使用大小写混合的标识符.

    Avoid mixed-case identifiers in Postgres if possible.

    • PostgreSQL列名是否区分大小写?

    不确定为什么有IF array_length(tags, 1) > 0 THEN，但是可能可以替换为IF tags IS NOT NULL THEN或完全不替换IF，然后再跟踪IF NOT FOUND THEN.更多:

    Not sure why you have IF array_length(tags, 1) > 0 THEN, but can probably be replaced with IF tags IS NOT NULL THEN or no IF at all and follow up with IF NOT FOUND THEN. More:

    • 将动态SQL(EXECUTE)作为IF的条件声明

    这篇关于将标签数组传递给plpgsql函数，并在WHERE条件下使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！