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.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
