Dynamically cast type of array elements to match some expression type in PostgreSQL query

Question

I want to use array_position function in PostgreSQL (which takes array of some type and expression or value of the same type) for constructing query that returns rows in some arbitrary order (additional context: I want to enhance Ruby on Rails in_order_of feature which is currently implemented via unreadable CASE statement):

SELECT id, title, type
FROM posts
ORDER BY
  array_position(ARRAY['SuperSpecial','Special','Ordinary']::varchar[], type),
  published_at DESC;

The problem here is that requirement to do explicit type casting from type inferred by PostgreSQL from array literal (ARRAY['doh'] is text[]) to type of expression (type is varchar here). While varchar and text are coercible to each other, PostgreSQL requires explicit type cast, otherwise if omit it (like in array_position(ARRAY['doh'], type)) PostgreSQL will throw error (see this answer for details):

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

While it is not a problem to specify explicit type cast in some static queries, it is problem in autogenerated queries when type of expression is unknown beforehand: array_position(ARRAY[1,2,3], id * 2) (what type has id * 2?)

I thought that pg_typeof() could help me, but it seems that it can't be used neither in :: operator nor in CAST operator (I've seen information that both forms aren't function forms, but syntax constructs, see this question for details):

SELECT id, title, type
FROM posts
ORDER BY array_position(CAST(ARRAY['SpecialPost','Post','Whatever'] AS pg_typeof(type)), type), id;

ERROR:  type "pg_typeof" does not exist
LINE 1: ...on(CAST(ARRAY['SpecialPost','Post','Whatever'] AS pg_typeof(...

Question:
How to do dynamic typecast to expression type (say, to type of "posts"."id" * 2) in the same SQL query?

I would prefer to avoid extra roundtrip to database server (like executing SELECT pg_typeof("id" * 2) FROM "posts" LIMIT 1 and then using its result in generating of a new query) or writing some custom functions. Is it possible?

Answer 1

Better query

I want to enhance Ruby on Rails in_order_of feature which is currently implemented via unreadable CASE statement:

For starters, neither the awkward CASE construct nor array_position() are ideal solutions.

SELECT id, title, type
FROM posts
ORDER BY
  array_position(ARRAY['SuperSpecial','Special','Ordinary']::varchar[], type),
  published_at DESC;

There is a superior solution in Postgres:

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;

This avoids calling the function array_position() for every row and is cheaper.
Equivalent short syntax with array literal and implicit column name:

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

db<>fiddle here

Added "benefit": it works with type-mismatch in Postgres 13 - as long as array type and column type are compatible.

The only possible caveat I can think of: If the passed array has duplicate elements, joined rows are duplicated accordingly. That wouldn't happen with array_position(). But duplicates would be nonsense for the expressed purpose in any case. Make sure to pass unique array elements.

See:

• ORDER BY the IN value list
• PostgreSQL unnest() with element number

Improved functionality in Postgres 14

The error you report is going away with Postgres 14:

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

Quoting the release notes:

• Allow some array functions to operate on a mix of compatible data types (Tom Lane)

  The functions array_append(), array_prepend(), array_cat(), array_position(), array_positions(), array_remove(), array_replace(), and width_bucket() now take anycompatiblearray instead of anyarray arguments. This makes them less fussy about exact matches of argument types.

And the manual on anycompatiblearray:

Indicates that a function accepts any array data type, with automatic promotion of multiple arguments to a common data type

So, while this raises the above error msg in Postgres 13:

SELECT array_position(ARRAY['a','b','c']::text[], 'd'::varchar);

.. the same just works in Postgres 14.

(Your query and error msg show flipped positions for text and varchar, but all the same.)

To be clear, calls with compatible types now just work, incompatible types still raise an exception:

SELECT array_position('{1,2,3}'::text[], 3);

(The numeric literal 3 defaults to type integer, which is incompatible with text.)

Answer to actual question

.. which may be irrelevant by now. But as proof of concept:

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$;

db<>fiddle here

Doesn't make a whole lot of sense, as the type of posts.id should be well-known at the time of writing the function, but there may be special cases ...

Now both of these calls work:

SELECT * FROM posts_order_by('{SuperSpecial,Special,Ordinary}'::varchar[]);    
SELECT * FROM posts_order_by('{1,2,3}'::int[]);

Though the second typically doesn't make sense.

Related, with links to more:

• Executing queries dynamically in PL/pgSQL