PostgreSQL：带有 ANY($1) 的 PREPARED 语句

发布于 2024-11-16 05:37:15 字数 443 浏览 4 评论 0原文

我想创建一个准备语句，其中输入是一些计数未知的整数。我尝试做的方式是

PREPARE prep_select_data AS SELECT ARRAY[$1, id], $1, * FROM tbl_data WHERE id = ANY($1);

我得到的错误是：

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: ...ELECT ARRAY[$1, id], $1, * FROM tbl_data WHERE id = ANY($1);
                                                             ^

我无法弄清楚问题。

请指教。

问候，
玛雅克

原文

I want to create a PREPARED STATEMENT where inputs are some integers whose count is not known.
The way I'm trying to do is

PREPARE prep_select_data AS SELECT ARRAY[$1, id], $1, * FROM tbl_data WHERE id = ANY($1);

The error I'm getting is:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: ...ELECT ARRAY[$1, id], $1, * FROM tbl_data WHERE id = ANY($1);
                                                             ^

I'm not able to figure out the issue.

Please advise.

Regards,
Mayank

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

多像笑话 2024-11-23 05:37:15

由于错误表明您需要将 $1 指定为（整数）数组：

-- DEALLOCATE prep_select_data;
PREPARE prep_select_data (int[]) AS 
SELECT
    $1 || ARRAY[id], $1, *
FROM
    tbl_data 
WHERE
    id = ANY($1);

我将 ARRAY[$1, id] 更改为 $1 || ARRAY[id]，所以结果看起来像：

EXECUTE prep_select_data ('{1, 2}');
 ?column? | ?column? | id | value 
----------+----------+----+-------
 {1,2,1}  | {1,2}    |  1 | aaa
 {1,2,2}  | {1,2}    |  2 | bbb

这是因为（来自 doc ):

多维数组必须具有
每个维度的匹配范围。一个
不匹配会导致错误报告。

As error says you need to specify your $1 as array (of integers):

-- DEALLOCATE prep_select_data;
PREPARE prep_select_data (int[]) AS 
SELECT
    $1 || ARRAY[id], $1, *
FROM
    tbl_data 
WHERE
    id = ANY($1);

I changed ARRAY[$1, id] to $1 || ARRAY[id], so result looks like:

EXECUTE prep_select_data ('{1, 2}');
 ?column? | ?column? | id | value 
----------+----------+----+-------
 {1,2,1}  | {1,2}    |  1 | aaa
 {1,2,2}  | {1,2}    |  2 | bbb

It's because (from doc):