Search code examples
sqlxmlpostgresqlvolatile

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

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?

Solution

  • 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.