Hi,
I have a quasi materialized view that's maintained by INS, UPD, and
DEL triggers on several child tables.
The tables involved have different structures, but I needed a single
view for selecting records based on a few common fields. This
approach is much faster than querying the separate tables and trying
to correlate and sort the results.
materialized view - view_a
child tables - table_a, table_b, table_c
Here's my question- what's the fastest what to retrieve rows from each
of the child tables after I get results from view_a ?
I don't like using temp tables in Postgres (too much pain in the
past), so first selecting into a temp table which could subsequently
be joined against the child tables isn't appealing to me.
The result set from materialized view_a will never exceed 60 rows, so
I'm thinking about this:
a) LOOP on a SELECT FROM view_a
b) for each record, add the row id to one of 3 comma delimited strings
(one per child table)
c) perform a SELECT WHERE IN (delimited_string) from each child table
Any comments? I guess I'm mainly concerned about the speed of the FOR
SELECT LOOP...