Re: Help - moving data to new table structure

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