Re: XML ouput for psql

Peter Eisentraut kirjutas N, 06.03.2003 kell 00:37:
> greg@turnstep.com writes:
> 
> > I think all psql needs is a simple output, similar to the ones used by
> > Oracle, Sybase, and MySQL; the calling application should then process
> > it in some way as needed (obviously this is not for interactive use).
> > Where can one find a "standard table model?"
> 
> I think for processing-oriented output, the system described in the
> SQL/XML standard draft is the way to go.  Considering the people who wrote
> it, it's probably pulled from, or bound to appear in, a major commercial
> database.
> 
> I also think that psql is not the place to implement something like this.
> It's most likely best put in the backend, as a function like
> 
>     xmlfoo('select * from t1;')
> 
> Then any interface and application that likes it, not just psql-based
> ones, can use it.

I have written an aggregate function in pl/python for my own needs that
returns underlying query fomatted as XML, but it has some problems:

1) both the row-to-xml-fragment and
collect-the-fragments-to-wellformed-xml-doc have to be defined for each
and every different query (the actual function text is the same).

2) it is unneccesaryly hard to define a function that takes a record as
argument - the record type is lost: for even simple things like this

select * from (select * from mytable) mtab; 

the result of inner query is _not_ of rowtype mytable, i.e.

you can do

select xmlfrag(mytable) from mytable;

but not

select xmlfrag(mytable) from (select * from mytable) mytable;


----------------
Hannu