PostgreSQL-按数组排序

Question

我有2个表格-包含课程ID和课程名称的课程以及包含每个课程标签的tagCourse。

course                    tagcourse
------------            ----------------
PK id_course            PK tag
   name                 PK, FK id_course

我想编写一个函数，该函数按给定的标签数组搜索课程，并按匹配标签的数量将其返回。但是我不知道如何正确，有效地编写它。请帮我。

IE。

CREATE OR REPLACE FUNCTION searchByTags(tags varchar[])
RETURNS SETOF.....
  RETURN QUERY SELECT * FROM course c INNER JOIN tagcourse tc ON c.id_course = tc.id_course
  WHERE ???  ORDER BY ???

END....

Answer 1

CREATE OR REPLACE FUNCTION search_by_tags(tags varchar[])
RETURNS TABLE (id_course integer, name text, tag_ct integer) AS
$func$
SELECT id_course, c.name, ct.tag_ct
FROM (
SELECT tc.id_course, count(*)::int AS tag_ct
FROM unnest($1) x(tag)
JOIN tagcourse tc USING (tag)
GROUP BY 1 – first aggregate ..
) AS ct
JOIN course c USING (id_course) – .. then join
ORDER BY ct.tag_ct DESC – more columns to break ties?
$func$ LANGUAGE sql;

• 用于unnest()从您的输入数组生成表。

• 您不需要为此使用plpgsql。使用简单的SQL函数更简单。

• 我使用unnest($1)（带有位置参数）代替unnest(tags)，因为后者仅对SQL函数中的PostgreSQL 9.2+有效（与plpgsql不同）。我在这里引用手册：

在较早的数字方法中，使用以下语法引用参数$n：$1引用第一个输入参数，引用第二个输入参数，$2依此类推。无论是否使用名称声明了特定参数，这都将起作用。

• count()返回bigint。您需要将其强制转换int为与声明的返回类型匹配，或者将返回的列声明为bigint开头。

• 使用USING（equi-joins）：USING (tag)而不是简化语法的完美时机ON tc.tag = c.tag。

• 通常， 先 聚合 然后再 连接到另一个表通常会更快。减少所需的联接操作。 根据注释中@Clodoaldo的问题，这是一个 SQL
  Fiddle 来演示区别。

• OTOH，如果在连接后进行聚合，则不需要子查询。较短，但可能较慢：

  SELECT c.id_course, c.name, count(*)::int AS tag_ct
  FROM unnest($1) x(tag)
  JOIN tagcourse tc USING (tag)
  JOIN course c USING (id_course)
  GROUP BY 1
  ORDER BY 3 DESC; – more columns to break ties?