Inserting XML nodes using PLPGSQL

Question

I am using PLPGSQL to process a XML column called XMLland. I have a column filled with information on land that I am not able to change but I can add to.

As part of a loop that I am aware is functoning, due to other statements, I am looking to insert some additional XML nodes. However I am not familiar with PLPGSQL specific functions. I am using REC to loop through the table.

An example of the current XML is

<area>
    <type>
        mixed
    </type>
    <population>
        10,000
    </population>
</area>

I am looking to insert a new node into

<tree>
    <height>
        20m
    </height>
    <density>
        6
    </density>
</tree>

Here is the update statement I am using

UPDATE "dbLand".tbl_duration
SET XMLland.modify('
insert <tree><height>rec.height</height><density>rec.density</density>    </tree> as first
into (/area)[1]')
WHERE referee = 'abc'

I am getting the error syntax error at or near "(" SET oidetails.modify('

Is this a syntax issue, or is not possible to use .modify() in PLPGSQL

Help greatly appreciated

Answer 1

It is whole wrong - You cannot to modify some parts of XML document with UPDATE statement - this statement can be used for modification of some column value of some table.

UPDATE table SET field WHERE constrains 

XML documents in PostgreSQL are immutable - that means, you cannot to edit document, you have to create new changed document and old document should be replaced by new document. More - XML document in PostgreSQL is +/- string - you can modify XML like string.

postgres=# SELECT * FROM data;
┌───────────────────┐
│         d         │
╞═══════════════════╡
│ <area>           ↵│
│     <type>       ↵│
│         mixed    ↵│
│     </type>      ↵│
│     <population> ↵│
│         10,000   ↵│
│     </population>↵│
│ </area>           │
└───────────────────┘
(1 row)

postgres=# UPDATE data SET d = replace(d::text, '</area>', 
'    <height>
       20m
    </height>
    <density>
       6
    </density>
 </area>')::xml;
UPDATE 1

postgres=# SELECT d FROM data;
┌───────────────────┐
│         d         │
╞═══════════════════╡
│ <area>           ↵│
│     <type>       ↵│
│         mixed    ↵│
│     </type>      ↵│
│     <population> ↵│
│         10,000   ↵│
│     </population>↵│
│     <height>     ↵│
│         20m      ↵│
│     </height>    ↵│
│     <density>    ↵│
│         6        ↵│
│     </density>   ↵│
│ </area>           │
└───────────────────┘
(1 row)

The PostgreSQL has not any special functions for any updates of XML functions. If you need it, then you can install plv8 - javascript engine for stored functions, and you can use any related functionality of javascript.