PostgreSQL: Declare a cursor for prepared statement
Clash Royale CLAN TAG#URR8PPP
PostgreSQL: Declare a cursor for prepared statement
The following DECLARE fails:
DECLARE
PREPARE stmt(bigint) AS SELECT ...;
DECLARE crs CURSOR FOR stmt;
According to https://www.postgresql.org/docs/9.6/static/sql-declare.html, stmt has to be either SELECT or VALUES command.
stmt
SELECT
VALUES
I use PREPARE statement in a latency-critical section of the code in which thousands of quick queries are emitted. Parsing and generating a query plan each time would be performance killer. However, in some rare cases the query can return millions of records and the result doesn't fit into memory.
PREPARE
Is there a way to declare a cursor for prepared statement in PostgreSQL? If not, are there any workarounds?
FETCH
That's right. I want to combine the high latency of prepared statements with the ability to fetch results part by part.
– Marcin Barczyński
Jan 12 at 16:27
I think it's impossible. but let lions and bears say their word. It would look like
declare l CURSOR FOR execute... or prepare a as declare both make no sense to me– Vao Tsun
Jan 12 at 16:28
declare l CURSOR FOR execute
prepare a as declare
One way I can think of is to add LIMIT clause to the prepared statement, and check if the limit has been reached. If so, declare a cursor for the same query but now without the LIMIT clause. Is there any other way?
– Marcin Barczyński
Jan 12 at 16:31
please update post with code. I dont understand your idea
– Vao Tsun
Jan 12 at 16:36
1 Answer
1
https://www.postgresql.org/docs/9.1/static/ecpg-commands.html#ECPG-EXECUTING
This can be done through ECPG C library in c code.
One can create prepared statement in C code as:
EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
and then create cursor for that statement as:
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;
fetch result form cursor within infinite loop.
EXEC SQL OPEN foo_bar USING 100;
while(1)
EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
This is available with 9.1 version of postgresql.
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
prepared stmt is for "Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement." and cant be used with cursors. your question is if you can somehow
FETCHfrom prepared select?..– Vao Tsun
Jan 12 at 16:19