Uwe
In attempting to understand the underlying intent of the statement, I have a
question.
What purpose does the statement "myrow" serve in this expression.
Bob
----- Original Message -----
From: "Uwe C. Schroeder" <uwe@xxxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, November 10, 2005 5:05 PM
Subject: 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@xxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>
Sent: Thursday, November 10, 2005 3:34 PM
Subject: Re: 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 5: don't forget to increase your free space map settings
