Re: Help - moving data to new table structure - Mailing list pgsql-general

From Jakub Ouhrabka
Subject Re: Help - moving data to new table structure
Date
Msg-id Pine.LNX.4.44.0301091525220.17205-100000@server
Whole thread Raw
In response to Help - moving data to new table structure  ("Brian Johnson" <bjohnson@jecinc.on.ca>)
List 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 by date:

Previous
From: Tom Lane
Date:
Subject: Re: Feature-request: allowing the date/time type to accept ISO8601 compliant date/time combinations on input
Next
From: "Brian Johnson"
Date:
Subject: Re: Help - moving data to new table structure