Using query_to_xml in PostgreSQL with prepared statements

Question

I'm using PostgreSQL's function query_to_xml function to generate XML of a query result.

SELECT * FROM query_to_xml( 'SELECT * from some_table WHERE id = ?',true,false,'')

Problem is, when I use it from JDBC, with prepared Statements the '?' is ignored, so Postgres says:

"The column index is out of range..."

Is there any possible solution to pass parameters to such a query ?

Daniel Vérité · Accepted Answer · 2013-02-15 15:04:43Z

2

Try to move the ? outside the string literal:

SELECT * FROM query_to_xml( 'SELECT * from some_table WHERE id = '||?,true,false,'')

answered Feb 15, 2013 at 15:04

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thanks, that works out! I suppose, even if I need an ORDER BY, i can concat it by || ?

I think you'd write: ..WHERE id= '||?||' ORDER BY...',true,...,