我有2个表 – 课程包含课程的id和名称以及包含每门课程标签的tagCourse.
course tagcourse ------------ ---------------- PK id_course PK tag name PK,FK id_course
我想编写一个函数,通过给定的标签数组搜索课程,并按照匹配标签的数量对它们进行排序.但是我不知道如何以有效的方式正确地编写它.请帮帮我.
即.
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....
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()从输入数组生成表,如already demonstrated by @Clodoaldo.
>你不需要plpgsql.使用普通的sql函数更简单.
>我使用unexst($1)(带位置参数)而不是unexst(标签),因为后者仅对sql函数中的Postgresql 9.2有效(与plpgsql不同). I quote the manual here：
In the older numeric approach,arguments are referenced using the
Syntax$n@H_502_23@:$1@H_502_23@ refers to the first input argument,$2@H_502_23@ to the second,
and so on. This will work whether or not the particular argument was
declared with a name.
> count()返回bigint.您需要将其强制转换为int以匹配声明的返回类型,或者将返回的列声明为bigint开头.
>使用USING(equi-joins)简化语法的完美场合：USING(tag)而不是ON tc.tag = c.tag.
>首次聚合通常会更快,然后加入另一个表.减少所需的连接操作.
根据@Clodoaldo in the comments的问题,这是一个SQL Fiddle,以证明其中的差异.
> OTOH,如果在连接后聚合,则不需要子查询.更短,但可能更慢：
SELECT c.id_course,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?
