Search code examples
sqlxmlpostgresql

Create and pass a system identifier to a function in PostgreSQL


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.


Solution

  • 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")