pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLooking for some help with cascading updates...
7+ messages / 3 participants
[nested] [flat]
* Looking for some help with cascading updates...
@ 2006-04-18 18:19 Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 7+ messages in thread
From: Mark True @ 2006-04-18 18:19 UTC (permalink / raw)
To: pgsql-sql
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
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Looking for some help with cascading updates...
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
@ 2006-04-18 18:31 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 18:44 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Kretschmer @ 2006-04-18 18:31 UTC (permalink / raw)
To: pgsql-sql
Mark True <darfoo@gmail.com> 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°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Looking for some help with cascading updates...
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
@ 2006-04-18 18:44 ` Mark True <darfoo@gmail.com>
2006-04-18 18:49 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 7+ messages in thread
From: Mark True @ 2006-04-18 18:44 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
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 <akretschmer@spamfence.net> wrote:
>
> Mark True <darfoo@gmail.com> 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°, E 13.56889°
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Looking for some help with cascading updates...
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 18:44 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
@ 2006-04-18 18:49 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 19:08 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Kretschmer @ 2006-04-18 18:49 UTC (permalink / raw)
To: pgsql-sql
Mark True <darfoo@gmail.com> 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;
begin
select 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 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°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Looking for some help with cascading updates...
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 18:44 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:49 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
@ 2006-04-18 19:08 ` Mark True <darfoo@gmail.com>
2006-04-18 19:21 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 19:49 ` Re: Looking for some help with cascading updates... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Mark True @ 2006-04-18 19:08 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
Next silly question, how do you get your database to like plpgsql...
I do CREATE LANGUAGE 'plpgsql' and it says it is not defined?
--Mark
On 4/18/06, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
>
> Mark True <darfoo@gmail.com> 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;
> begin
> select 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 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°, E 13.56889°
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Looking for some help with cascading updates...
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 18:44 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:49 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 19:08 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
@ 2006-04-18 19:21 ` Andreas Kretschmer <akretschmer@spamfence.net>
1 sibling, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2006-04-18 19:21 UTC (permalink / raw)
To: pgsql-sql
Mark True <darfoo@gmail.com> schrieb:
> Next silly question, how do you get your database to like plpgsql...
- createlang on the command line
- create language plpgsql; (in psql)
>
> I do CREATE LANGUAGE 'plpgsql' and it says it is not defined?
Strange...
>
> --Mark
>
>
> On 4/18/06, AAnnddrreeaass KKrreettsscchhmmeerr <_a_k_r_e_t_s_c_h_m_e_r_@_s_p_a_m_f_e_n_c_e_._n_e_t> wrote:
> Mark True <_d_a_r_f_o_o_@_g_m_a_i_l_._c_o_m> schrieb:
Please, without HTML and fullquote...
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°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Looking for some help with cascading updates...
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 18:44 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:49 ` Re: Looking for some help with cascading updates... Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 19:08 ` Re: Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
@ 2006-04-18 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-04-18 19:49 UTC (permalink / raw)
To: Mark True <darfoo@gmail.com>; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-sql
"Mark True" <darfoo@gmail.com> writes:
> I do CREATE LANGUAGE 'plpgsql' and it says it is not defined?
Should work (in 8.1 anyway). Could we see the *exact* command and
error message?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-04-18 19:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-18 18:19 Looking for some help with cascading updates... Mark True <darfoo@gmail.com>
2006-04-18 18:31 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 18:44 ` Mark True <darfoo@gmail.com>
2006-04-18 18:49 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 19:08 ` Mark True <darfoo@gmail.com>
2006-04-18 19:21 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-04-18 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox