Postgresql: How to change the value of an attribute in an XML column?

plpgsql, postgresql, postgresql-9.1, xml

Solution

You can use XML::LibXML in a PL/PerlU function (or PL/Perl, if you can preload XML::LibXML in `plperl.on_init` in your `postgresql.conf`) like this:

CREATE EXTENSION IF NOT EXISTS plperlu;

CREATE OR REPLACE FUNCTION replace_attribute(
    xml   xml,
    attr  text,
    val   text
) RETURNS XML LANGUAGE plperlu AS $$
    my ($xml, $attr, $val) = @_;
    use XML::LibXML;
    my $dom = XML::LibXML->load_xml(string => $xml);
    for my $elem ($dom->findnodes("*[\@$attr]")) {
        $elem->setAttribute($attr => $val);
    }
    return $dom->serialize;
$$;

Then you can use it like so:

try=# SELECT replace_attribute(
    '<foo id="blah">hi</foo>',
    'id',
    'whatever'
);
      replace_attribute      
-----------------------------
 <foo id="whatever">hi</foo>+

There is quite a bit of flexibility to be had in the XPath expression to `findnodes()`, in case you need more than that.

Problem

I need to change the value of an XML attribute stored in a column using the XML datatype in a table before I return it to the client. Right now I use a cursor, get xml, cast it to `VARCHAR`, parse it, change the attribute value (or add it if it is not present), put it in temporary table and return data from temporary table. Everything is done in a stored procedure (pl/pgsql). I wonder if there is cleaner way to do this?

Original source