PREPARE SQL plan for Postgresql - with bind varible list -
i have sql-command want use prepare statment googling found syntax :
prepare sqlplan (bigint) select * employees employee id in ($1); execute usrrptplan2(123);
i have 2 question :
how can use list/array (size unknown) in bind variable part?
this sqls works - want can use
"prepare plan if not exists ?"
since running second time get: error: prepared statement "sqlplan" exists
https://www.postgresql.org/docs/current/static/sql-prepare.html
prepare if not exists
not work - have https://www.postgresql.org/docs/current/static/sql-deallocate.html deallocate
first.
regarding array in argument, eg:
t=# prepare a(text[]) select * pg_class relname = any($1); prepare t=# execute ('{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)
wiki
Comments
Post a Comment