[SQL] Looking for some help with cascading updates...
Here is the question:I have a situation where I need to create triggers to cascade an insert operation to many tables to maintain foreign key constraints. So at a high levelINSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.', '01832')
myTable has a relation to two other tables, which contain Zip and Address. Before I insert into myTable I want to:Check if '3 Dingle St.' exists in table Address, if not insert itCheck if '01832' exists in table Zip and if not insert it
I want to generalize this so that for any insert into myTable, I guarantee that if Zip and Address are not populated they will be. My insert into myTable always contains full information required for doing the inserts into the other related tables.
Help?--Mark
Re: [SQL] Looking for some help with cascading updates...
I guess I should have clarified. I am having trouble figuring out how to construct the IF statement to see if that item exists in the foreign table something like:count=select count(*) from table;if count=0 {
insert fkey into myOtherTable}On 4/18/06, Andreas Kretschmer <[EMAIL PROTECTED]> wrote:
Mark True <[EMAIL PROTECTED]> schrieb:
>> Here is the question:>> I have a situation where I need to create triggers to cascade an insert> operation to many tables to maintain foreign key constraints.>> So at a high level
>> INSERT INTO myTable (Name, Address, Zip) VALUES ('Mark', '3 Dingle St.',> '01832')>> myTable has a relation to two other tables, which contain Zip and Address.> Before I insert into myTable I want to:
>> Check if '3 Dingle St.' exists in table Address, if not insert it> Check if '01832' exists in table Zip and if not insert itFor such task i write a function (plpgsql). This obtains all parameters,
do the checks and insert into the other tables and the table with the fkconstraints.HTH, Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°---(end of broadcast)---
TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Looking for some help with cascading updates...
Next silly question, how do you get your database to like plpgsql...I do CREATE LANGUAGE 'plpgsql' and it says it is not defined?--MarkOn 4/18/06,
Andreas Kretschmer <[EMAIL PROTECTED]> wrote:
Mark True <[EMAIL PROTECTED]> schrieb:> I guess I should have clarified. I am having trouble figuring out how to> construct the IF statement to see if that item exists in the foreign table
> something like:>> count=select count(*) from table;>> if count=0 {> insert fkey into myOtherTable> }a real example:(table harz_ufpos references harz_uf)
create or replace function gh_insert(text,int,int,text,int,float) returns int as $$declare c int;beginselect into c count(*) from harz_uf where (uf,ab) = ($1,$2);if c = 0 then
insert into harz_uf (uf,ab,beschreibung,aktiv) values ($1,$2,$4,'t');end if;insert into harz_ufpos (uf,pos,anzahl,rest,flaeche) values ($1,$3,$5,$5,$6);return 1;
end;$$ language 'plpgsql';HTH, Andreas--Really, I'm not out to destroy Microsoft. That will just be a completelyunintentional side effect. (Linus Torvalds)"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
