hubert depesz lubaczewski wrote:
> On 1/4/07, Richard Huxton <dev@archonet.com> 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