My main goal is to only create/output an xml element when it is not empty. If it is empty NULL
should be returned. I'm using the native xmlelement()
function for this. So if I call SELECT xmlelement(name "my-element", NULL)
it will return <my-element/>
while my goal is to return NULL
in this case.
I tried to simply wrap the xmlelement
function like below, so that it will return NULL
when any input parameter is NULL
:
CREATE OR REPLACE FUNCTION xml_noempty_element(label name, data xml) RETURNS xml AS
$$
SELECT xmlelement(label, data)
$$
LANGUAGE SQL IMMUTABLE STRICT;
Sadly this doesn't validate.
Is it even possible to create a PostgreSQL function that takes a system identifier? And if yes how can I create and pass the identifier/name to the function?
I know that I can do something like the following code to achieve my main goal:
SELECT CASE
WHEN EXISTS(SELECT FROM my_xml_rows) THEN
xmlelement(name "my-element",
( SELECT xmlagg(xmlelement) FROM my_xml_rows )
)
END;
However this will call the query twice which I try to avoid.
I guess one other option I have is to wrap a NULLIF((QUERY), '<my-element/>')
around.
The only option I found to somehow pass system identifiers to a function is using Dynamic SQL. This allows passing the system identifier as a string/text.
CREATE OR REPLACE FUNCTION xml_noempty_element(tag text, data xml) RETURNS xml AS
$$
DECLARE
retval xml;
BEGIN
EXECUTE format('SELECT xmlelement(name %I,$1)', $1) USING $2 INTO retval;
RETURN retval;
END
$$
LANGUAGE plpgsql IMMUTABLE STRICT;
-- <outer><inner>value</inner></outer>
SELECT xml_noempty_element('outer', xmlelement(name "inner", 'value'));
-- NULL
SELECT xml_noempty_element('outer', NULL);
Note: To solve my main goal I used xmlforest(), which returns NULL
if the given content is also NULL
, instead of returning an empty XML element like xmlelement()
does.
SELECT xmlforest('MyValue' AS "ElementTagName")