sql - 动态转换数组元素的类型以匹配 PostgreSQL 查询中的某些表达式类型

Question

我想在 PostgreSQL 中使用 array_position 函数(它采用某种类型的数组和相同类型的表达式或值)来构造以任意顺序返回行的查询(附加上下文:我想增强当前实现的 Ruby on Rails in_order_of feature通过不可读的 CASE 语句):

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

这里的问题是，需要根据 PostgreSQL 从数组文字 (ARRAY['doh'] is text[]) 推断出的类型进行显式类型转换以键入表达式(type 在这里是 varchar)。虽然 varchar 和 text 可以相互强制转换，但 PostgreSQL 需要显式类型转换，否则如果省略它(如 array_position(ARRAY['doh'], type )) PostgreSQL会抛出错误(详见this answer):

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.

虽然在某些静态查询中指定显式类型转换不是问题，但在事先不知道表达式类型的自动生成查询中就会出现问题:array_position(ARRAY[1,2,3], id * 2 )(什么类型有id * 2？)

我以为 pg_typeof() 可以帮助我，但它似乎不能在 :: 运算符和 CAST 运算符中使用(我看过资料这两种形式都不是函数形式，而是语法结构，详见 this question):

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(...

问题:
如何在同一 SQL 查询中对表达式类型(例如 "posts"."id"* 2)进行动态类型转换？

我宁愿避免额外的数据库服务器往返(比如执行 SELECT pg_typeof("id"* 2) FROM "posts"LIMIT 1 然后使用它的结果生成一个新的查询)或者写一些自定义函数。可能吗？

Answer 1

更好的查询

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

对于初学者来说，笨拙的 CASE 构造和 array_position() 都不是理想的解决方案。

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

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;

这避免了为每一行调用函数 array_position() 并且成本更低。
具有数组文字和隐式列名称的等效短语法:

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

添加“好处”:它适用于 Postgres 13 中的类型不匹配 - 只要数组类型和列类型兼容。

我能想到的唯一可能的警告:如果传递的数组有重复的元素，连接的行将相应地重复。 array_position() 不会发生这种情况。但在任何情况下，重复对于表达的目的都是无稽之谈。确保传递唯一的数组元素。

参见:

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

Postgres 14 中的改进功能

您报告的错误将随 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.

引用 release notes :

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

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

还有 the manual on anycompatiblearray :

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

因此，虽然这会在 Postgres 13 中引发上述错误消息:

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

.. 这同样适用于 Postgres 14。

(您的查询和错误消息显示 text 和 varchar 的翻转位置，但都一样。)

需要明确的是，使用兼容类型的调用现在可以正常工作，不兼容类型仍然会引发异常:

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

(数字文字3默认为integer类型，这与text不兼容。)

实际问题的答案

.. 现在可能已经无关紧要了。但作为概念证明:

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

没有多大意义，因为 posts.id 的类型在编写函数时应该是众所周知的，但可能有特殊情况......

现在这两个调用都起作用了:

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

虽然第二个通常没有意义。

相关的，带有指向更多内容的链接:

• Executing queries dynamically in PL/pgSQL