Pass Array Of Tags To A Plpgsql Function And Use It In Where Condition

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. Here

Solution 1:

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

• PostgreSQL parameterized Order By / Limit in table function

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

CREATEOR REPLACE FUNCTION get_items_by_tag(VARIADIC tags text[])
  RETURNSTABLE (id int, title text, tag text[]) AS
$func$
BEGIN
   IF array_length(tags, 1) >0THEN-- NO need for EXECUTERETURN 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 constructGROUPBY d.id;               -- PK covers whole table-- array_to_string(tags, ',') -- no need to convert array with ANY-- ELSE ...END IF;
END
$func$  LANGUAGE plpgsql;

Call with actual array:

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');

Major points

• Use RETURN QUERY to actually return resulting rows.

  • PostgreSQL function returning multiple result sets

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

• Use an ANY construct instead of IN. Why?

  • How to use ANY instead of IN in a WHERE clause with Rails?

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

  • Pass multiple values in single parameter

• Avoid mixed-case identifiers in Postgres if possible.

  • Are PostgreSQL column names case-sensitive?

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:

• Dynamic SQL (EXECUTE) as condition for IF statement

Solution 2:

Try to use in place of tags in format statement this: '''' || array_to_string(tags, "','") || '''' the result in the IN clause will be like IN ('gaming','sport').

Solution 3:

It's because I'm not returning anything.

return query 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 = ANY(%L)
        GROUPBY d.id, d.title
        ', tags) ;