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:
,
| [EMAIL PROTECTED]:~$ cat input.txt
| copy "namen" from stdin;
| 10 Magdalena Kretschmer
| 11 Katharina Kretschmer
| [EMAIL PROTECTED]:~$
`
,
| 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° ;-)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
