Hello iam trying to use xmltable in function, to update some data in other table, as for input i got string
This is a test program the final result will be that the insert should be created from any xml doing some data adjustment
the xml look like this
<header>
<data>
<line>
<field1>some data in f1</field1>
<field2>other data f2</field2>
<field3>this data contains numers 012323</field3>
<field4>and the last data</field4>
<line>
</data>
</header>
Iam taking the next steps
create table with casted string to xml input values
create table if not exists test_xml_table as select input_string::xml as string_xml;
will do some data adjustments
final step is insert
insert into test_tab( field1, field2, field3, field4 )
select xt.field1, xt.field2, xt.field3, field4 from test_xml_table
cross join xmltable( '/data/line' passing string_xml
columns field1 text path 'field1', field2 text path 'field2',
field3 text path 'field3', field4 text path 'field4' ) as xt;
The problem is that if the table test_xml_table doesnt exists, the program dont create it ( still didnt see it after create table command. I tried to do workaround, create the table first and fill it with XML data but now i dont know what to put after PASSING phrase. There is no error just no data is inserted. Will be grateful for help
the whole code is below
create or replace function test_function(
input_string character varying )
RETURNS void
LANGUAGE plpgsql
as $BODY$
begin
create table test_xml_table as select input_string::xml as string_xml;
insert into test_tab( field1, field2, field3, field4 )
select xt.field1, xt.field2, xt.field3, field4 from test_xml_table
cross join xmltable( '/data/line' passing string_xml
columns field1 text path 'field1', field2 text path 'field2',
field3 text path 'field3', field4 text path 'field4' ) as xt;
end;
$BODY$;
call ->
select test_function( '<header>
<data>
<line>
<field1>some data in f1</field1>
<field2>other data f2</field2>
<field3>this data contains numers 012323</field3>
<field4>and the last data</field4>
</line>
</data>
</header>' )
Tried to use postgres xmltable function to insert data into table. Iam expecting that the data from function input that is type character varying will insert data
cross join is not needed.
The xml namespace_uri should be /header/data/line not just /data/line
create or replace function test_function( input_string character varying )
RETURNS void
LANGUAGE plpgsql
as $BODY$
begin
create table test_xml_table as select input_string::xml as string_xml;
insert into test_tab( field1, field2, field3, field4 )
select xt.field1, xt.field2, xt.field3, field4
from test_xml_table, xmltable( '/header/data/line' passing string_xml
columns
field1 text path 'field1',
field2 text path 'field2',
field3 text path 'field3',
field4 text path 'field4'
) as xt;
end;
$BODY$;