Re: PostgreSQL and SOAP, suggestions? - Mailing list pgsql-hackers

That function looks great, but what happens if you need to return 1 
million records? Wouldn't you exhaust all the memory in the server? Or 
can you stream it somehow?

I have an actual libpq program which performs a query against a server, 
and will stream out the XML, so the number of records has very little 
affect on efficiency. I think the table2xml function is great for 99% of 
all the queries, but for those huge resultsets, I think it may be 
problematic.

What do you think?

BTW, I routinely have queries that return millions of rows.


Peter Eisentraut wrote:

>mlw writes:
>
>  
>
>>Given a HTTP formatted query:
>>GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2"
>>
>>The output is entered below.
>>    
>>
>
>That looks a lot like the SQL/XML-style output plus a SOAP header.  Below
>is the output that I get from the SQL/XML function that I wrote.  A simple
>XSLT stylesheet should do the trick for you.
>
>Btw., I also have an XSLT stylesheet that can make an HTML table out of
>this output and I have a table function that can generate a virtual table
>from this output.
>
>
>=> select table2xml('select * from products');
>
><?xml version='1.0'?>
><table
>    xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
>    xsi:noNamespaceSchemaLocation='#'> <!-- XXX this needs to be fixed -->
>
><xsd:schema
>    xmlns:xsd='http://www.w3.org/2001/XMLSchema'
>    xmlns:sqlxml='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'>
>
>  <xsd:import
>      namespace='http://www.iso-standards.org/mra/9075/2001/12/sqlxml'
>      schemaLocation='http://www.iso-standards.org/mra/9075/2001/12/sqlxml.xsd' />
>
><xsd:simpleType name='peter.pg_catalog.text'>
>  <xsd:restriction base='xsd:string'>
>    <xsd:maxLength value='MLIT' /> <!-- XXX needs actual value -->
>  </xsd:restriction>
></xsd:simpleType>
>
><xsd:simpleType name='INTEGER'>
>  <xsd:restriction base='xsd:integer'>
>    <xsd:maxInclusive value='2147483647'/>
>    <xsd:minInclusive value='-2147483648'/>
>  </xsd:restriction>
></xsd:simpleType>
>
><xsd:simpleType name='NUMERIC'>
>  <xsd:restriction base='xsd:decimal'>
>    <xsd:totalDigits value='PLIT'/> <!-- XXX needs actual values -->
>    <xsd:fractionDigits value='SLIT'/>
>  </xsd:restriction>
></xsd:simpleType>
>
><xsd:complexType name='RowType'>
>  <xsd:sequence>
>    <xsd:element name='name' type='peter.pg_catalog.text' nillable='true'></xsd:element>
>    <xsd:element name='category' type='INTEGER' nillable='true'></xsd:element>
>    <xsd:element name='price' type='NUMERIC' nillable='true'></xsd:element>
>  </xsd:sequence>
></xsd:complexType>
>
><xsd:complexType name='TableType'>
>  <xsd:sequence>
>    <xsd:element name='row' type='RowType' minOccurs='0' maxOccurs='unbounded' />
>  </xsd:sequence>
></xsd:complexType>
>
><xsd:element name='table' type='TableType' />
>
></xsd:schema>
>
>  <row>
>    <name>screwdriver</name>
>    <category>3</category>
>    <price>7.99</price>
>  </row>
>
>  <row>
>    <name>drill</name>
>    <category>9</category>
>    <price>12.49</price>
>  </row>
>
></table>
>
>  
>