From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help with extracting large volumes of records across related
Date: Mon, 13 Sep 2004 12:58:57 GMT
Message-ID: <lQg1d.213339$X12.73127@edtnps84> (raw)
In-Reply-To: <004b01c49986$228c3d60$6e01a8c0@a31p005>
References: <004b01c49986$228c3d60$6e01a8c0@a31p005>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ischamay.andbergsay@activestateway.com
Subject: Re: Help with extracting large volumes of records across related
In-Reply-To: <lQg1d.213339$X12.73127@edtnps84>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox