Re: Help - moving data to new table structure

Thanks - I'll look into this further (I haven't played with functions before)

PS - the new table structure is NOT better than the existing one (it sucks), but I
have to meld my data into the existing table structure of another app (modifying
the new app's table structure is not currently an option)


Jakub Ouhrabka (jouh8664@ss1000.ms.mff.cuni.cz) wrote*:
>
>Hi,
>
>I'm not sure if your new design is better than the older but you perhaps
>know what you are doing...
>
>Try to solve it with a plpgsql function:
>
>CREATE OR REPLACE FUNCTION contact_type(contacts.contact_pk%TYPE) RETURNS
>VARCHAR AS '
>DECLARE
>  a_pk ALIAS FOR $1;
>  l_contact_type VARCHAR;
>  lr_rec RECORD;
>BEGIN
>  l_contact_type := '''';
>  FOR lr_rec IN SELECT * FROM contact_types WHERE contact_pk = a_pk LOOP
>    l_contact_type := l_contact_type || '','' || lr_rec.contact_type;
>  END LOOP;
>
>  l_contact_type := substr(l_contact_type, 2);
>
>  RETURN l_contact_type;
>END;
>' LANGUAGE 'plpgsql';
>
>and then you can do something like this:
>
>CREATE TABLE new_contacts AS SELECT *, contact_type(contact_pk) FROM
>contacts;
>
>Totally untested but you have the idea...
>
>It is also possible to write your own aggregate function to do this but it
>is probably overkill...
>
>HTH,                   kuba
>
>
>
>
>On Thu, 9 Jan 2003, Brian Johnson wrote:
>
>> I'm working on moving contact data from MS Access to some predefined tables in
>> PostgreSQL (part of another application)
>>
>> I need some help creating one of the append queries
>>
>> The Access tables include a table called "Contact Type" that is linked
>> to "Contacts" (the main table for the contact data) with a one to many type
>> relationship (ie each contact can have many types attached to it)
>>
>> The new table layout in PostgreSQL will allow multiple contact types, but they
are
>> listed as comma separated values in one field.  The php code in this app then
>> matches up the comma separated values with corresponding records in the "Contact
>> Type" table
>>
>> How the heck do I make a query to transfer the data to this new format?
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>