1CREATE SCHEMA testxmlschema; 2 3CREATE TABLE testxmlschema.test1 (a int, b text); 4INSERT INTO testxmlschema.test1 VALUES (1, 'one'), (2, 'two'), (-1, null); 5CREATE DOMAIN testxmldomain AS varchar; 6CREATE TABLE testxmlschema.test2 (z int, y varchar(500), x char(6), w numeric(9,2), v smallint, u bigint, t real, s time, r timestamp, q date, p xml, o testxmldomain, n bool, m bytea, aaa text); 7ALTER TABLE testxmlschema.test2 DROP COLUMN aaa; 8INSERT INTO testxmlschema.test2 VALUES (55, 'abc', 'def', 98.6, 2, 999, 0, '21:07', '2009-06-08 21:07:30', '2009-06-08', NULL, 'ABC', true, 'XYZ'); 9 10SELECT table_to_xml('testxmlschema.test1', false, false, ''); 11SELECT table_to_xml('testxmlschema.test1', true, false, 'foo'); 12SELECT table_to_xml('testxmlschema.test1', false, true, ''); 13SELECT table_to_xml('testxmlschema.test1', true, true, ''); 14SELECT table_to_xml('testxmlschema.test2', false, false, ''); 15 16SELECT table_to_xmlschema('testxmlschema.test1', false, false, ''); 17SELECT table_to_xmlschema('testxmlschema.test1', true, false, ''); 18SELECT table_to_xmlschema('testxmlschema.test1', false, true, 'foo'); 19SELECT table_to_xmlschema('testxmlschema.test1', true, true, ''); 20SELECT table_to_xmlschema('testxmlschema.test2', false, false, ''); 21 22SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, false, ''); 23SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, false, ''); 24SELECT table_to_xml_and_xmlschema('testxmlschema.test1', false, true, ''); 25SELECT table_to_xml_and_xmlschema('testxmlschema.test1', true, true, 'foo'); 26 27SELECT query_to_xml('SELECT * FROM testxmlschema.test1', false, false, ''); 28SELECT query_to_xmlschema('SELECT * FROM testxmlschema.test1', false, false, ''); 29SELECT query_to_xml_and_xmlschema('SELECT * FROM testxmlschema.test1', true, true, ''); 30 31DECLARE xc CURSOR WITH HOLD FOR SELECT * FROM testxmlschema.test1 ORDER BY 1, 2; 32SELECT cursor_to_xml('xc'::refcursor, 5, false, true, ''); 33SELECT cursor_to_xmlschema('xc'::refcursor, false, true, ''); 34MOVE BACKWARD ALL IN xc; 35SELECT cursor_to_xml('xc'::refcursor, 5, true, false, ''); 36SELECT cursor_to_xmlschema('xc'::refcursor, true, false, ''); 37 38SELECT schema_to_xml('testxmlschema', false, true, ''); 39SELECT schema_to_xml('testxmlschema', true, false, ''); 40SELECT schema_to_xmlschema('testxmlschema', false, true, ''); 41SELECT schema_to_xmlschema('testxmlschema', true, false, ''); 42SELECT schema_to_xml_and_xmlschema('testxmlschema', true, true, 'foo'); 43 44 45-- test that domains are transformed like their base types 46 47CREATE DOMAIN testboolxmldomain AS bool; 48CREATE DOMAIN testdatexmldomain AS date; 49 50CREATE TABLE testxmlschema.test3 51 AS SELECT true c1, 52 true::testboolxmldomain c2, 53 '2013-02-21'::date c3, 54 '2013-02-21'::testdatexmldomain c4; 55 56SELECT xmlforest(c1, c2, c3, c4) FROM testxmlschema.test3; 57SELECT table_to_xml('testxmlschema.test3', true, true, ''); 58