CODEWITHSUNDEEP
xmlpostgresqlplpgsqlpostgresql-9.1
tester.one
tester.one

Reputation: 369

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

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?

Upvotes: 4

Views: 5891

Answers (2)

roman
roman

Reputation: 117520

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')

Upvotes: 3

theory
theory

Reputation: 9897

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.

Upvotes: 4

Related Questions