Re: Where

This will work if you can guarantee that it's only one record

INSERT INTO pipe (fluid_id,contain) SELECT (fluid_id,contain) FROM process
WHERE contain='ip'

otherwise (more than one record) you have to loop over the resultset,
something like (off the top of my head)

create or replace function base() returns trigger as $$
DECLARE
myrow RECORD;
BEGIN
insert into pipe (fluid_id) values (new.fluid_id);
for myrow in select * from process where contain = 'ip' loop
insert into pipe(fluid_id,contain) values (row.fluid_id,row.contain);
if not found then
raise exception 'error creating record';
end if;
end loop;
return NULL;
END;

On Thursday 10 November 2005 15:56, Bob Pawley wrote:
> Thank you - what is the correct command???
>
> Bob
>
> ----- Original Message -----
> From: "Uwe C. Schroeder" <uwe(at)oss4u(dot)com>
> To: <pgsql-general(at)postgresql(dot)org>
> Cc: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
> Sent: Thursday, November 10, 2005 3:34 PM
> Subject: Re: [GENERAL] Where
>
> > SELECT INTO xxxx
> > tries to create table xxxx
> > See: http://www.postgresql.org/docs/8.0/interactive/sql-selectinto.html
> >
> > Why do you do the select into anyways? It does nothing.
> > If you try to update table pipe with the select result you have the wrong
> > command.
> >
> > UC
> >
> > On Thursday 10 November 2005 14:24, Bob Pawley wrote:
> >> 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
> >
> > --
> > UC
> >
> > --
> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> > Phone: +1 650 872 2425 San Bruno, CA 94066
> > Cell: +1 650 302 2405 United States
> > Fax: +1 650 872 2417

--
UC

--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417