Duplicate rows
I have the following expression working in that the process.fluid_id is transfereed to pipe.fluid_id when the column - process.contain has a value of 'ip'.
There is no transfer when the contain column holds other values. Success - so far.
How do I keep the table pipe from being populated with duplicate rows? Among other reasons not to have duplicate rows, I want to make pipe.fluid_id a primary key.
Bob
CREATE TABLE pipe ( fluid_id int4 NOT NULL);
CREATE TABLE process( fluid_id int4 NOT NULL, process varchar, contain varchar) ;
create or replace function base() returns trigger as $$
DECLARE
myrow RECORD;
BEGIN
for myrow in select * from process where contain = 'ip' loop
insert into pipe(fluid_id) values (myrow.fluid_id);
if not found then
do nothing ;
end if;
end loop;
return NULL;
END;
$$ language plpgsql;
create trigger trig1 after insert on process
for each row execute procedure base();
insert into process (fluid_id, process, contain)
values ('1', 'water3', 'ip');
so what's the problem exactly??
what's holding you from adding the primary key over fluid_id ??
in the trigger, you could use an if exists to check if the row is there before
and i guess there is no need for a loop? you can do the same per row.
Show quoted text
On 11/12/05, Bob Pawley <rjpawley@shaw.ca> wrote:
I have the following expression working in that the process.fluid_id is
transfereed to pipe.fluid_id when the column - process.contain has a value
of 'ip'.
There is no transfer when the contain column holds other values. Success -
so far.How do I keep the table pipe from being populated with duplicate rows? Among
other reasons not to have duplicate rows, I want to make pipe.fluid_id a
primary key.Bob
CREATE TABLE pipe ( fluid_id int4 NOT NULL);
CREATE TABLE process( fluid_id int4 NOT NULL, process varchar, contain
varchar) ;create or replace function base() returns trigger as $$
DECLARE
myrow RECORD;
BEGINfor myrow in select * from process where contain = 'ip' loop
insert into pipe(fluid_id) values (myrow.fluid_id);
if not found then
do nothing ;end if;
end loop;
return NULL;
END;
$$ language plpgsql;create trigger trig1 after insert on process
for each row execute procedure base();insert into process (fluid_id, process, contain)
values ('1', 'water3', 'ip');