Re: Where - Mailing list pgsql-general

one full row - NOT two or more rows.


On Thursday 10 November 2005 17:23, Bob Pawley wrote:
> By 'one record' do you mean one full row or one column of one row??
>
> Bob
>
> ----- Original Message -----
> From: "Uwe C. Schroeder" <uwe@oss4u.com>
> To: "Bob Pawley" <rjpawley@shaw.ca>
> Cc: <pgsql-general@postgresql.org>
> Sent: Thursday, November 10, 2005 5:05 PM
> Subject: Re: [GENERAL] 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@oss4u.com>
> >> To: <pgsql-general@postgresql.org>
> >> Cc: "Bob Pawley" <rjpawley@shaw.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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq

--
    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