Skip to content
Advertisement

PostgreSQL – order by an array

I have 2 tables – course that contains id and name of the courses and tagCourse that contains tags for each course.

I’d like to write a function that searches courses by given array of tags and returns them ordered by quantity of matching tags. However I don’t know how to write it correctly and in an efficient way. Please help me.

ie.

Advertisement

Answer

Use unnest() to produce a table from your input array, like already demonstrated by @Clodoaldo.

You don’t need plpgsql for this. Simpler with a plain SQL function.

I use unnest($1) (with positional parameter) instead of unnest(tags), since the later is only valid for PostgreSQL 9.2+ in SQL functions (unlike plpgsql). The manual:

In the older numeric approach, arguments are referenced using the syntax $n: $1 refers to the first input argument, $2 to the second, and so on. This will work whether or not the particular argument was declared with a name.

count() returns bigint. You need to cast it to int to match the declared return type or declare the the returned column as bigint to begin with.

Perfect occasion to simplify the syntax a bit with USING (equi-joins): USING (tag) instead of ON tc.tag = c.tag.

It’s regularly faster to first aggregate, then join to another table. Reduces the needed join operations.
To address @Clodoaldo’s comments, here is a fiddle demonstrating the difference:

db<>fiddle here
Old sqlfiddle

OTOH, if you aggregate after the join, you don’t need a subquery. Shorter, but probably slower:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement