X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5C34611F6059 for ; Tue, 18 Apr 2006 15:44:48 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.713 X-Spam-Level: X-Spam-Status: No, score=0.713 required=5 tests=[AWL=0.712, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 4eKoh4tQ-yRQ for ; Tue, 18 Apr 2006 15:44:39 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.196]) by postgresql.org (Postfix) with ESMTP id 4883C11F6B1A for ; Tue, 18 Apr 2006 15:44:39 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id z3so787068nzf for ; Tue, 18 Apr 2006 11:44:29 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=MUtEo4cz4men06Ku9N5XmqiaJMCss8HOSXdVH+5x0Raz/EgqXsLsko9+IsAgSZfQB3NJCvFvqEaQl9fN9bu9vkYWJXOZgbPRbxdYmGqORoY/XIiIDFDdh9Sg5Qkw4CSqTEBzRMLBUU/2T2oPV4o+BESuuvFJD5RvJeiN81Qmlek= Received: by 10.64.233.20 with SMTP id f20mr65785qbh; Tue, 18 Apr 2006 11:44:29 -0700 (PDT) Received: by 10.65.197.10 with HTTP; Tue, 18 Apr 2006 11:44:29 -0700 (PDT) Message-ID: <647d31c20604181144g63b9ecc2t800e4e2d5fa91f0e@mail.gmail.com> Date: Tue, 18 Apr 2006 14:44:29 -0400 From: "Mark True" To: "Andreas Kretschmer" Subject: Re: Looking for some help with cascading updates... Cc: pgsql-sql@postgresql.org In-Reply-To: <20060418183101.GA31339@KanotixBox> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_23705_1174368.1145385869094" References: <647d31c20604181119m43ba3c7ay3d467dd9446a6fd8@mail.gmail.com> <20060418183101.GA31339@KanotixBox> X-Archive-Number: 200604/116 X-Sequence-Number: 24925 ------=_Part_23705_1174368.1145385869094 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3Dselect count(*) from table; if count=3D0 { insert fkey into myOtherTable } On 4/18/06, Andreas Kretschmer wrote: > > Mark True 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 it > > For 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 fk > constraints. > > > HTH, Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknow) > Kaufbach, Saxony, Germany, Europe. N 51.05082=B0, E 13.56889= =B0 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ------=_Part_23705_1174368.1145385869094 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 ta= ble something like:

count=3Dselect count(*) from table;

if co= unt=3D0 {
insert fkey into myOtherTable
}


On 4/18/06, Andreas Kretschmer &l= t;akretschmer@spamfence.net> wrote:
Mark True = <darfoo@gmail.com> schrieb:
>
> Here is the question:
>
> I have a situation w= here I need to create triggers to cascade an insert
> operation to ma= ny 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 t= wo other tables, which contain Zip and Address.
> Before I insert int= o  myTable I want to:
>
> Check if '3 Dingle St.' exists in table Address, if not in= sert it
> Check if '01832' exists in table Zip and if not insert it
For such task i write a function (plpgsql). This obtains all paramete= rs,
do the checks and insert into the other tables and the table with the f= k
constraints.


HTH, Andreas
--
Really, I'm not out to d= estroy Microsoft. That will just be a completely
unintentional side effe= ct.            =             &nb= sp;     (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."&= nbsp;   (unknow)
Kaufbach, Saxony, Germany, Europe. =             &nb= sp;N 51.05082=B0, E 13.56889=B0

---------------------------(end of b= roadcast)---------------------------
TIP 4: Have you searched our list archives?

   &n= bsp;           http://archives.postgresql.org

------=_Part_23705_1174368.1145385869094--