Re: Help with extracting large volumes of records across related

Damien Dougan wrote:
> Basically we have a number of tables, which are exposed as 2 public
> views (say PvA and PvB). For each row in PvA, there are a number of
> related rows in PvB (this number is arbitrary, which is one of the
> reasons why it cant be expressed as additional columns in PvA - so we
> really need 2 sets of tables - which leads to two sets of extract calls
> - interwoven to associate PvA with PvB).
>
> Are there any tools/tricks/tips with regards to extracting large volumes
> of data across related tables from Postgres? It doesnt have to export
> into XML, we can do post-processing on the extracted data as needed -
> the important thing is to keep the relationship between PvA and PvB on a
> row-by-row basis.

Just recently had to come up with an alternative to MSSQL's "SQL..FOR
XML", for some five-level nested docs, that turned out to be faster (!)
and easier to understand:

Use SQL to organize each of the row types into a single text field, plus
a single key field, as well as any filter fields you . Sort the union,
and have the reading process break them into documents.

For example, if PvA has key (account_id, order_id) and
fields(order_date, ship_date) and PvB has key (order_id, product_id) and
fields (order_qty, back_order)

CREATE VIEW PvABxml AS
SELECT    account_id::text + order_id::text AS quay
    ,'order_date="' + order_date::text
    + '" ship_date="' + ship_date::text + '"' AS info
    ,ship_date
FROM    PvA
    UNION ALL
SELECT    account_id::text + order_id::text + product_id::text
    ,'order_qty="' + order_qty::text +'"'
    ,ship_date
FROM    PvA JOIN PvB USING (order_id)

Then:

SELECT quay, info
FROM pvABxml
WHERE ship_date = '...'
ORDER BY quay

gives you a stream of info in the (parent,child,child...
parent,child,child...) order you want, that assemble very easily into
XML documents. If you need to pick out, say, orders where there are
backordered items, you probably need to work with a temp table with
which to prefilter.