Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT

PetSerAl <petseral@gmail.com> writes:
>>   CREATE OR REPLACE FUNCTION xml_to_text_no_inline(pXml xml) RETURNS text
>>   LANGUAGE sql
>>   IMMUTABLE
>>   SET search_path = pg_catalog
>>   AS $$
>>   SELECT CASE WHEN pXml IS DOCUMENT
>>               THEN (xpath('/*/text()', pXml))[1]::text
>>               ELSE pXml::text
>>          END;
>>   $$;

> There is bug in that function. Expectation, that `xpath('/*/text()',
> pXml)` will be evaluate only after successful `pXml IS DOCUMENT`
> check, is not supported by documentation.

Yeah, CASE is not strong enough to prevent constant-folding in this
context.  You could try something like

    create or replace function xml_to_text(pXml xml) returns text
    as $$
    select
      coalesce(
        (xpath('/*/text()',
               case when pXml is document then pXml else null end))[1],
        pXml
      )::text;
    $$ language sql immutable;

This works because xpath() is strict so it won't try to do anything
with a NULL input, just return NULL; and then the COALESCE() serves
the purpose of injecting pXml when that happens.

			regards, tom lane