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

Question

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?

I don't know of a function to replace attributes in XML. But maybe your code could still be improved. Would help if included it in the question. — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 26, 2012 at 13:22

theory · Accepted Answer · 2012-05-06 06:25:30Z

4

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.

answered May 6, 2012 at 6:25

theory

9,96912 gold badges68 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tester.one Over a year ago

Thanks, one more question: do I need to convert xml datatype to string or can pl/perl work directly with xml datatype? Should I worry about the overhead if this function would be called a lot?

theory Over a year ago

It will automatically be cast to text before it is passed to PL/Perl. That's what the example here does.

roman · Accepted Answer · 2013-08-26 17:43:46Z

3

Python version:

create or replace function replace_attrib(data xml, attr text, val text)
returns xml as
$$
import xml.etree.ElementTree as ET
r = ET.fromstring(data)
r.attrib[attr] = val
return ET.tostring(r)
$$ language plpython3u;

select replace_attrib('<foo id="blah">hi</foo>', 'id', 'whatever')

answered Aug 26, 2013 at 17:43

roman

118k30 gold badges205 silver badges209 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related