I am attempting to transfer the data in the fluid_id column of table process into column fluid_id of table pipe.
This should happen only when column contain of table process holds the value 'ip'.
Here is the command that I am having trouble with.
-------
create table process (fluid_id integer primary key, process varchar, contain varchar);
create table pipe ( fluid_id integer not null, contain varchar);
create or replace function base() returns trigger as $$
begin
insert into pipe (fluid_id) values (new.fluid_id);
select * into pipe from process where contain = 'ip';
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 ('2', 'water', 'ip');
-------------------
On inserting data this error comes back -
-------
ERROR: relation "pipe" already exists
CONTEXT: SQL statement "SELECT * INTO pipe from process where contain = 'ip'"
PL/pgSQL function "base" line 4 at SQL statement
------
Of course the table pipe does already exist - it is a permanent table.
Is the program looking for some other target?? Perhaps a temporary table??
Or am I completely screwed up???
Bob