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)
>
