On Nov 6, 2005, at 3:19 PM, Bob Pawley wrote:
Last week I received help form the list in inserting a serial row
of one table (process) into a row in a table called specification.
I was able to expand that to include inserting the same information
into a table called pipe.
-------------------
create or replace function base() returns trigger as $$
begin
insert into specification (fluid_id) values
(new.fluid_id);
insert into pipe (fluid_id) values (new.fluid_id);
return null;
end;
$$ language plpgsql;
create trigger trig1 after insert on process
for each row execute procedure base();
----------------
This works well.
Now I want to modify the program so that only those rows from
process that have ip in the column ip_op_reactor are inserted into
pipe.
Following is my best result after studying every piece of
documentation available to me.
------------------
create or replace function base() returns trigger as $$
begin
insert into specification (fluid_id) values
(new.fluid_id);
select fluid_id as fi from process pr;
select ip_op_reactor as iop from pr ;
select fluid_id as fi from pipe pi
(select fi
from pr where iop = 'ip',
insert into pi (fi) values (new.fi));
return null;
end;
$$ language plpgsql;
create trigger trig1 after insert on process
for each row execute procedure base();
-----------------
This is the error I get.
------------------
ERROR: SELECT query has no destination for result data
HINT: If you want to discard the results, use PERFORM instead.
CONTEXT: PL/pgSQL function "base" line 4 at SQL statement
----------------
I would very much appreciate any help as to where I am going wrong.
Thanks
Might be helpful to re-read the chapter on Basic Statements in PL/pgSQL:
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html
For SELECTs in PL/pgSQL, you either need a target (via INTO) or you
need to use PERFORM instead.
That's what the HINT is about. It thinks because you're not
specifying a target for your SELECTs that you might want to discard
the results.
If (as I assume) you don't, you'll probably also want to declare
variables to serve as targets for the results of your SELECTs.
--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
