Re: Where - Mailing list pgsql-general

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