Is it databases in general, SQL or Postgresql?

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