Re: Where - Mailing list pgsql-general

Thanks again.

I'll give it a try.

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 7:44 PM
Subject: Re: [GENERAL] Where


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq