Re: Subqueries

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)