Обсуждение: Help - moving data to new table structure

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?

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

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