Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TEMP TABLE posts (id int PRIMARY KEY, title text, type text, published_at timestamp);
INSERT INTO posts VALUES
  (1, 'title1', 'Some Type'   , '2021-11-04 12:00')
, (2, 'title2', 'Special'     , '2021-11-04 13:00')
, (3, 'title3', 'Ordinary'    , '2021-11-04 14:00')
, (4, 'title4', 'SuperSpecial', '2021-11-04 15:00')
, (5, 'title5', NULL          , '2021-11-04 16:00')
;

CREATE TABLE



INSERT 0 5


-- better
SELECT id, title, type
FROM   posts
LEFT   JOIN unnest(ARRAY['SuperSpecial','Special','Ordinary']::varchar[]) WITH ORDINALITY o(type, ord) USING (type)
ORDER  BY o.ord, published_at DESC;


id
title
type


4
title4
SuperSpecial

2
title2
Special

3
title3
Ordinary

5
title5
null

1
title1
Some Type



SELECT 5


-- short syntax
SELECT id, title, type
FROM   posts
LEFT   JOIN unnest('{SuperSpecial,Special,Ordinary}'::varchar[]) WITH ORDINALITY type USING (type)
ORDER  BY ordinality, published_at DESC;


id
title
type


4
title4
SuperSpecial

2
title2
Special

3
title3
Ordinary

5
title5
null

1
title1
Some Type



SELECT 5


-- original (fails in Postgres 13, and slow if it works)
SELECT id, title, type
FROM posts
ORDER BY
  array_position(ARRAY['SuperSpecial','Special','Ordinary']::varchar[], type),
  published_at DESC;

ERROR:  function array_position(character varying[], text) does not exist
LINE 5:   array_position(ARRAY['SuperSpecial','Special','Ordinary']:...
          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


-- dynamic function
CREATE OR REPLACE FUNCTION posts_order_by(_order anyarray)
 RETURNS SETOF posts
 LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format (
   $$
   SELECT p.*
   FROM   posts p
   LEFT   JOIN unnest($1) WITH ORDINALITY o(type, ord) ON (o.type::%s = p.type)
   ORDER  BY o.ord, published_at DESC
   $$
  , (SELECT atttypid::regtype
     FROM   pg_attribute
     WHERE  attrelid = 'posts'::regclass
     AND    attname = 'type')
   )
   USING _order;
END
$func$;

CREATE FUNCTION


-- still works
SELECT * FROM posts_order_by('{SuperSpecial,Special,Ordinary}'::varchar[]);


id
title
type
published_at


4
title4
SuperSpecial
2021-11-04 15:00:00

2
title2
Special
2021-11-04 13:00:00

3
title3
Ordinary
2021-11-04 14:00:00

5
title5
null
2021-11-04 16:00:00

1
title1
Some Type
2021-11-04 12:00:00



SELECT 5


-- this "works" too, now (though rather pointless):
SELECT * FROM posts_order_by('{1,2,3}'::int[]);


id
title
type
published_at


5
title5
null
2021-11-04 16:00:00

4
title4
SuperSpecial
2021-11-04 15:00:00

3
title3
Ordinary
2021-11-04 14:00:00

2
title2
Special
2021-11-04 13:00:00

1
title1
Some Type
2021-11-04 12:00:00



SELECT 5

id	title	type
4	title4	SuperSpecial
2	title2	Special
3	title3	Ordinary
5	title5	null
1	title1	Some Type

id	title	type	published_at
4	title4	SuperSpecial	2021-11-04 15:00:00
2	title2	Special	2021-11-04 13:00:00
3	title3	Ordinary	2021-11-04 14:00:00
5	title5	null	2021-11-04 16:00:00
1	title1	Some Type	2021-11-04 12:00:00