|
Hope someone can help me learn.
Sample 1 below, does work. It transfers every serial number generated
by table pr into table pi with no duplication.
Sample 2 below, does not work. From a logical (perhaps naive) extension of
Sample 1, I adapted the function to identify which of the serial numbers in
table pr is to be transferred to table pi. I am attempting to do
this as part of the database structure not as data retrieval.
Could someone explain to me why this isn't acceptable as a simple basic
function?
Could someone explain to me what needs to be changed, enhanced or modified
to make this database structure work?
Thanks in advance.
Bob
Sample 1 create table pr (Process_Name varchar (60) not null, Fluid_ID serial, Fluid varchar (30) not null, contain varchar (3), constraint pr_pk primary key (fluid_id)); create table pi (process_name varchar (60), Fluid_ID int4 not null, Fluid varchar (30), contain varchar (3), constraint pi_pk primary key (fluid_id), constraint pi_fluid_id foreign key (fluid_id) references pr (fluid_id) ); create or replace function base() returns trigger as $$ begin insert into pi (fluid_id) values (new.fluid_id); return null; end; $$ language plpgsql; create trigger trig1 after insert on pr for each row execute procedure base(); insert into pr (process_name, fluid, contain) values ('boiler_water', 'water','ip'); --------------------------------- Sample 2 create table pr (Process_Name varchar (60) not null, Fluid_ID serial, Fluid varchar (30) not null, contain varchar (3), constraint pr_pk primary key (fluid_id)); create table pi (process_name varchar (60), Fluid_ID int4 not null, Fluid varchar (30), contain varchar (3), constraint pi_pk primary key (fluid_id), constraint pi_fluid_id foreign key (fluid_id) references pr (fluid_id) ); create or replace function base() returns trigger as $$ begin insert into pi (fluid_id) values (new.fluid_id) where pr (contain) = 'ip'; return null; end; $$ language plpgsql; create trigger trig1 after insert on pr for each row execute procedure base(); insert into pr (process_name, fluid, contain) values ('boiler_water', 'water','ip'); Error Message ? ERROR: syntax error at or near "where" at character 41 QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) = 'ip' CONTEXT: PL/pgSQL function "base" line 2 at SQL statement |
