Re: How do I bump a row to the front of sort efficiently

> Or maybe instead of a view you could write a
> set-returning function, e.g. as described here:

I thought I'd see if I could make this work just for fun. Here is a
simple proof of concept (on 9.3):

-- DROP TABLE IF EXISTS topics;
CREATE TABLE topics (
id INTEGER PRIMARY KEY,
bumped_at INTEGER NOT NULL
);
INSERT INTO topics
SELECT a, a * 2
FROM generate_series(1, 1000) s(a)
;

CREATE OR REPLACE FUNCTION topics_sorted_after_id(INT, INT)
RETURNS TABLE(id int, after_top int, bumped_at int)
AS $$
SELECT id, 0 AS after_top, bumped_at
FROM topics
WHERE id = $1
UNION ALL
(SELECT id, 1 AS after_top, bumped_at
FROM topics
WHERE id IS DISTINCT FROM $1
ORDER BY bumped_at DESC
LIMIT $2 - 1)
ORDER BY after_top, bumped_at DESC
$$
LANGUAGE sql;

SELECT * FROM topics_sorted_after_id(45, 30);

That looks to me like it gives the right results. I'm curious if
RETURNS TABLE is the right approach to use here or if there is
something nicer.

What if the ORM insists on `FROM topics`? Is there any way to rewrite
the query or function to work around that?

Paul

--
_________________________________
Pulchritudo splendor veritatis.