I am quite new to postgresql and working with application team to migrate to postgresql from oracle.
When we are trying to use bind variable within BEGIN/END code block, it fails with
Caused by: java.sql.SQLException: The column index is out of range: 1, number of columns: 0. Query: DO $do$
[...]
What is the correct way to use bind variables in postgresql?
CREATE FUNCTION func(arg1 text, arg2 text) AS $$ SELECT arg1, arg2; $$ LANGUAGE sql; --or something like this
SELECT func(?, ?);
Explanation:
You cannot bind into a DO block because the content of the DO block is text and Java will not bind to question marks within text. Creating a formal function and then calling it using a normal SELECT statement with binding positions - i.e., typical function execution - is thus required.