Why does query_to_xml return an error for a volatile query in Postgres?

user_1063716 0
Viewed 46

I'd like to understand why something like this doesn't work in PostgreSQL:

SELECT query_to_xml('CREATE TABLE my_table (my_column INTEGER)', FALSE, TRUE, '')

It gives ERROR: CREATE TABLE is not allowed in a non-volatile function. Fair enough - but when I checked the signature for query_to_xml, it is marked as volatile:

CREATE OR REPLACE FUNCTION pg_catalog.query_to_xml(
    query text,
    nulls boolean,
    tableforest boolean,
    targetns text)
    RETURNS xml
    LANGUAGE 'internal'
    COST 100
    VOLATILE STRICT PARALLEL UNSAFE
AS [...]

Follow-up question: If this isn't allowed, is there any other way to run a dynamic volatile query without PL/PgSQL?

volatile postgresql xml sql
1 Answers

From the manual:

The following functions map the contents of relational tables to XML values.

That means you can't use this function for a CREATE TABLE statement, just SELECT data from a table and turn that into an XML.

The error message however, could be better, I agree.

user_2719590
Related