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
