Francy Francy - 1 month ago 16
SQL Question

PREPARE SQL plan for Postgresql - with bind varible list

I have an SQL-command I want to use with PREPARE statment
googling it I found the syntax is :

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


I have 2 question :


  1. How can I use a list/array (size unknown) in the Bind variable part?

  2. This Sqls works perfectly - but want can I do to use
    "Prepare plan if not exists ?"



Since Running it the second time i get:
ERROR: prepared statement "sqlPlan" already exists

Answer Source

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

PREPARE IF NOT EXISTS would not work - you have to https://www.postgresql.org/docs/current/static/sql-deallocate.html DEALLOCATE it first.

regarding array for in argument, eg:

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)