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)

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


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

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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to