Re: [despammed] update/insert data

am Sat, dem 27.11.2004, um 12:55:40 -0500 mailte Keith Worthington folgendes:
> Hi All,
>
> I have two tables in different schemas. The first table in the data_transfer
> schema is loaded with a COPY command. I need to transfer the data to the
> second schema inserting new records and updating existing records. What is
> the best way to achieve this functionality?

You can write a trigger-function. There you can do a insert/update for
everey new records in the data_transfer - table.

A simple example:

I hava 2 tables:

,----
| test_db=# \d namen;
| Tabelle »public.namen«
| Spalte | Typ | Attribute
| ----------+-------------------+-------------------------------------------------------
| id | integer | not null default nextval('public.namen_id_seq'::text)
| vorname | character varying |
| nachname | character varying |
| Trigger:
| trig1 BEFORE INSERT OR UPDATE ON namen FOR EACH ROW EXECUTE PROCEDURE trigg1()
|
| test_db=# \d namen2;
| Tabelle »public.namen2«
| Spalte | Typ | Attribute
| ----------+-------------------+-----------
| id | integer |
| vorname | character varying |
| nachname | character varying |
|
`----

And this trigger-function:

,----
| create or replace function trigg1() returns trigger as'
| begin
| insert into namen2 values (NEW.id, NEW.vorname, NEW.nachname);
| return NEW;
| end;
| ' language plpgsql;
`----

There isn't a check for update, but this is also possible.

,----
| test_db=# select * from namen;
| id | vorname | nachname
| ----+---------+----------
| (0 Zeilen)
|
| test_db=# select * from namen2;
| id | vorname | nachname
| ----+---------+----------
| (0 Zeilen)
`----

And i have a file:

,----
| kretschmer(at)kaufbach:~$ cat input.txt
| copy "namen" from stdin;
| 10 Magdalena Kretschmer
| 11 Katharina Kretschmer
| kretschmer(at)kaufbach:~$
`----

,----
| test_db=# \i input.txt
| test_db=# select * from namen;
| id | vorname | nachname
| ----+-----------+------------
| 10 | Magdalena | Kretschmer
| 11 | Katharina | Kretschmer
| (2 Zeilen)
|
| test_db=# select * from namen2;
| id | vorname | nachname
| ----+-----------+------------
| 10 | Magdalena | Kretschmer
| 11 | Katharina | Kretschmer
| (2 Zeilen)
`----

sorry about my bad english.
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)