Как вставить отдельно Node в xml поле в Postgresql?

Question

[Денис Кузнецов]

У меня есть следующая таблица

create table xml_test(
id serial primary key,
user_id integer,
xml_test_f xml
)

допустим я выполнил команду

insert into xml_test(user_id, xml_test_f) values (1, xml('<Audit>
										   		<User userId = "1" login = "user">
										   			<Action name = "REPLACEMENT" billId = "22">
										   				<Status>OPEN</Status>
													</Action>
										   		</User>
											   </Audit>'));

и вставил в нее соотв. xml

<Audit>
	<User userId = "1" login = "user">
		<Action name = "REPLACEMENT" billId = "22">
			<Status>OPEN</Status>
		</Action>
	</User>
</Audit>

теперь я хочу вставить новую ноду Action

update xml_test 
  set xmlelement(name 'Action', xmlattributes(23 as billId, 'REPLACEMENT' as 'name'), '<Status>CLOSED</STATUS>') 
  where user_id = 1;

ну это так на вскидку, потому что в идеале вместо 'Status' должна стоять еще одна нода
то есть я хочу вставить подобный xml node внутри ноды User у которой userId = 1

<Action name = 'REPLACEMENT' billId = 23>
	<Status>
		CLOSED
	<Status>
</Action>

но не могу понять как мне описать sql запрос для этого

Answer 1

[Денис Кузнецов]

Спустя полторы недели изучения документации, статей и видеоуроков я нашел следующее решение, которое сводится к тому, чтобы привести xml к text
Допустим у меня есть корень User и его потомки ноды Action

select unnest(xpath('//user', xml_test_f))::text from xml_test where id = 3;

<user userid="1" login="user">
  <action billid="22" name="REPLACEMENT">
    <status>CLOSED</status>
  </action>
  <action billid="11" name="REPLACEMENT"><status>OPEN</status></action><action/>
</user>

В конце списка действий при первоначальном создании документа вставляется закрытая пустая нода .
Создается следующая функция

create or replace function addNode(xmlId integer, newNode xml) 
returns void as 
$BODY$
declare oldXml text;

begin

select unnest(xpath('//user', xml_test_f))::text into oldXml from xml_test where id = 3;
update xml_test set  xml_test_f=xml(REPLACE(oldXml::text,'<action/>', newNode::text ||'<action/>')) where id = xmlId;

end
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;

В которой мы выбираем дерево от пользователя и редактируем его. При редактировании закрытая нода заменяется на ноду поданную на вход + закрытую ноду newNode::text ||''.
Теперь эту функцию можно использовать

Select addNode(3, xmlelement(name action, xmlattributes('11' as billId, 'REPLACEMENT' as name),
							xmlelement(name status, 'OPEN')));