Re: Help - moving data to new table structure
От | Jakub Ouhrabka |
---|---|
Тема | Re: Help - moving data to new table structure |
Дата | |
Msg-id | Pine.LNX.4.44.0301091525220.17205-100000@server обсуждение исходный текст |
Ответ на | Help - moving data to new table structure ("Brian Johnson" <bjohnson@jecinc.on.ca>) |
Список | pgsql-general |
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) >
В списке pgsql-general по дате отправления: