sql - 为 Postgresql 准备 SQL 计划 - 带有绑定(bind)变量列表

Question

我有一个 SQL 命令要与 ​​PREPARE 语句一起使用谷歌搜索我发现语法是:

PREPARE sqlPlan (bigint) AS
select * from employees 
where employee id in ($1);
EXECUTE usrrptplan2(123);

我有两个问题:

1. 如何在绑定(bind)变量部分使用列表/数组(大小未知)？

2. 此 Sqls 完美运行 - 但我想使用 "Prepare plan if not exists ?"

自从第二次运行后我得到:错误:准备好的语句“sqlPlan”已经存在

Answer 1

https://www.postgresql.org/docs/current/static/sql-prepare.html

PREPARE IF NOT EXISTS 将不起作用 - 你必须 https://www.postgresql.org/docs/current/static/sql-deallocate.htmlDEALLOCATE 首先。

关于参数中的数组，例如:

t=# prepare a(text[]) as select * from pg_class where relname = ANY($1);
PREPARE
t=# execute a ('{pg_tables,pg_indexes}');
  relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids |
 relhaspkey | relhasrules | relhastriggers | relhassubclass | relispopulated | relfrozenxid | relminmxid |    relacl     | reloptions
------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+
------------+-------------+----------------+----------------+----------------+--------------+------------+---------------+------------
 pg_tables  |           11 |   11075 |         0 |       10 |     0 |       11074 |             0 |        0 |         0 |             0 |             0 |             0 | f           | f           | p              | v       |        7 |         0 | f          |
 f          | t           | f              | f              | t              |            0 |          0 | {=r/postgres} |
 pg_indexes |           11 |   11083 |         0 |       10 |     0 |       11082 |             0 |        0 |         0 |             0 |             0 |             0 | f           | f           | p              | v       |        5 |         0 | f          |
 f          | t           | f              | f              | t              |            0 |          0 | {=r/postgres} |
(2 rows)