Search This Blog

Tuesday, March 13, 2012

Postgres using replace function; Postgres replacing phrase in an XML-typed column;

I wanted to replace column content '{ApplicationVersion}' with '{RegularMessage}' string. Postgres syntax seemed a little bit strange to me (because of the XMLPARSE) so I’m writing the solution here. As it turned I had to cast my content argument to text first as it was an XML-typed column - there is no automatic casting. If you forget to that the error pops up which suggests there is no replace function referenced but it actually means that there is no replace function with such arguments so you should cast your arguments:

ERROR: function replace(xml, unknown, unknown) does not exist

update message
set content = XMLPARSE(
DOCUMENT replace(text(content),'{ApplicationVersion}','{RegularMessage}')
where type = 'RegularMessage';

where content is the xml-typed column.

XMLPARSE can have DOCUMENT or CONTENT as arguments and you need to write one of the two keywords before the actual column name:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title></book>');
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>');


If you like this post, please leave a comment :)