hubert depesz lubaczewski wrote:
On 1/4/07, Richard Huxton <dev@xxxxxxxxxxxx> wrote:
Show me the table definitions and some sample data and I'll see if the
SQL is do-able.
technically - i can, but please - belive me it is not possible.
advert_custom_fields table has approx. 1200 columns (for reasons i was
explaining some time ago).
sample data would look like:
# select id, category_id from adverts order by id desc limit 5;
id | category_id
----------+-------------
35161391 | 35
35161390 | 35
35161389 | 230
35161388 | 34
35161387 | 37
(5 rows)
# select * from v_category_custom_fields limit 5;
category_id | codename | custom_field_name
-------------+-----------+-------------------
1 | contact | text_6
1 | web | text_5
1 | mail | text_4
1 | phone | text_3
1 | price_usd | number_3
(5 rows)
advert_custom_fields basically has id, advert_id, and then 128 column per
type (text, number, boolean, integer, date, time, timestamp).
OK, let's look at it one type at a time. You'd obviously generate the
following query via a script then save it as a view/prepared query.
SELECT advert_id, 'text_1'::text as colname, text_1 AS value
FROM advert_custom_fields
UNION ALL
SELECT advert_id, 'text_2'::text as colname, text_2 AS value
FROM advert_custom_fields
UNION ALL
...
SELECT advert_id, 'text_128'::text as colname, text_128 AS value
FROM advert_custom_fields;
Now that's going to run a set of seq-scans, so if the table's not going
to fit in RAM then you'll probably want to add a WHERE advert_id=xxx
part to each clause. Then call it once per advert-id in a loop as you
are at present. Or, you could do it in batches of e.g. 100 with a
partial index.
I'd be tempted to create a TEMP TABLE from that query, then join to the
table for the codename lookup via v_category_custom_fields. Of course,
you could do it all in the giant UNION ALL query if you wanted to.
--
Richard Huxton
Archonet Ltd
