Geek asks and answers

Postgresql: How to change attribute value in XML column?

I need to change the value of an XML attribute stored in a column using the XML data type in the table before I return it to the client.

At the moment, I am using the cursor , getting xml , discarding VARCHAR , parsing it, changing the attribute value (or adding it if it is missing), putting it in a temporary table and returning data from the temporary table. Everything is done in a stored procedure (pl / pgsql).

I wonder if there is a cleaner way to do this?

+3
source share
2 answers

XML:: LibXML PL/PerlU ( PL/Perl, XML:: LibXML plperl.on_init postgresql.conf) :

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;
$$;

:

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

XPath findnodes(), .

+2

Python:

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

More articles:


All Articles