postgresql - 对 WITH 变量执行选择查询

Question

通常，我在 postgres 中编写函数，如下所示

with detail_data as(
select
    id
from
    product p
where
    category = PARAM1_HERE 
order by update_time)

现在我想将查询更改为动态顺序。我尝试如下

with detail_data as( 
execute 'select id from product p where category  = $1 order by $2'
    using PARAM1_HERE,PARAM2_HERE)

编译时出现错误：

错误：“执行”处或附近的语法错误

我该如何解决？

标签： postgresqlfunction

Answer 1

它不起作用，因为它EXECUTE是一个 PL/pgSQL 命令，而不是SQL一个 - 以防您不使用准备好的语句。话虽如此，您可能想尝试使用真正的功能。

测试数据

CREATE TABLE product (id INT, category INT, txt TEXT);
INSERT INTO product VALUES (1,1,'x'),(42,1,'f'),(8,1,'a'),(2,1,'j');

功能

CREATE OR REPLACE FUNCTION cte_function(INT,TEXT) 
RETURNS TABLE(res INT) AS $$
BEGIN  
RETURN QUERY 
  EXECUTE FORMAT('SELECT id FROM product WHERE category=%L ORDER BY %I',$1,$2);
END; $$ LANGUAGE plpgsql;

测试（按 id 排序）

SELECT * FROM cte_function(1,'id') ;
 res 
-----
   1
   2
   8
  42
(4 rows)

测试（按txt排序）

SELECT * FROM cte_function(1,'txt') ;
 res 
-----
   8
  42
   2
   1
(4 rows)