CREATE OR REPLACE FUNCTION table_to_xml(text, text, text) RETURNS text AS $$

use XML::Writer;

my ($schema, $table, $filter, $output) = ($_[0], $_[1], $_[2], undef);
my $writer = new XML::Writer( OUTPUT => \$output, DATA_MODE => 1, DATA_INDENT => 4 );
my $query = <<SQL;
SELECT a.attname
FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE n.nspname = '$schema' AND c.relname = '$table'
  AND a.attrelid = c.oid AND n.oid = c.relnamespace
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
SQL
#elog NOTICE, $query;
my $fields = spi_exec_query($query);
my $nfields = $fields->{processed};

$query = 'SELECT * FROM ' . $schema . '.' . $table . ' ' . ($filter ne '' ? 'WHERE ' . $filter : '');
#elog NOTICE, $query;
my $rows = spi_exec_query($query);
my $nrows = $rows->{processed};

$writer->startTag( $table, 'xmlns:xsi' => 'http://www.w3.org/2001/XMLSchema-instance' );
foreach my $j (0 .. $nrows - 1) {
	$writer->startTag('row');
	my $row = $rows->{rows}[$j];
	foreach my $i (0 .. $nfields - 1) {
		my $field = $fields->{rows}[$i];
		my $field_name = $field->{attname};
		$writer->startTag($field_name);
		$writer->characters( $row->{$field_name} );
        	$writer->endTag($field_name);
	}
	$writer->endTag('row');
}
$writer->endTag($table);

return $output;

$$ LANGUAGE plperlu;

CREATE OR REPLACE FUNCTION table_to_xml(text, text) RETURNS text AS $$
  SELECT table_to_xml($1, $2, null);
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION table_to_xml(text) RETURNS text AS $$
  SELECT table_to_xml(current_schema(), $1, null);
$$ LANGUAGE sql;

SELECT table_to_xml('public', 'nova', 'id = 2');
SELECT table_to_xml('public', 'nova');
SELECT table_to_xml('nova');
