use query result in a second query

Hi experts,

I need to write a function that returns a recordset from a query based on the results of a prior query.  something like this (I assume that this will NOT work, but something that acheives that purpose would be perfect:

DECLARE
          the_name character varying := select thename from sometable where id = $1
BEGIN
          select * from othertable where name = the_name
END

does that make sense at all?

Thanks!

Regards,  Mike.
LVL 37
meverestAsked:
Who is Participating?
 
meverestConnect With a Mentor Author Commented:
OK, got it sorted! :)

--------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION replylist(character varying)
  RETURNS setof record AS
$BODY$
    DECLARE
        n character varying;
        r record;
    BEGIN
        n := name from names where id = $1;
        for r in select * from mytable where username = n
            LOOP
                RETURN next r;
            END LOOP;
    END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--------------------------------------------------------------------------------------------------------------------------------------

purrrfect!

thanks for trying! :)

Regards,  Mike.

0
 
SreejithGCommented:
Yes, this can be done using sub-query. Here is a sample.
select * from othertable where name = (select thename from sometable where id = $1)
Find more in help for sub-query
0
 
meverestAuthor Commented:
Hello,

thanks for that suggestion, but the context of this query makes it very inefficient to do that.

the actual query looks something like this:

select a, b, c from someothertable where id = $1
UNION
select a, b, c from someothertable1 where id = $1
UNION
select a, b, c from someothertable2 where id = $1
UNION
select a, b, c from someothertable3 where id = $1

and so on with very MANY unions of some quite complex queries (the actual query I am working with is already more than 250 lines!).  The issue is that if I try to use that sub-query method, then the query takes several minutes to complete! (i need it to return in just a couple of seconds!)

therefore, I am trying to work out how I can first evaluate that 'subquery' and then use that as argument to the main query, something like:

$2 = select name from themaintable where id = $1

select a, b, c from someothertable where id = $2
UNION
select a, b, c from someothertable1 where id = $2
UNION
select a, b, c from someothertable2 where id = $2
UNION
select a, b, c from someothertable3 where id = $2
0
 
SreejithGCommented:
Fine, then use temprary tables. Store the result of first query to a temporary table and use this temporary table in second query. Pease refer help on temporary tables.

Note: be carefull in using temporary tables. In some database you need to delete the tempory table after use. Other wise it will remain there and finally ends up outof disk space.
0
 
meverestAuthor Commented:
ok, thanks again, but that method is also not exactly what I am looking for.

I am getting close - this is something:
-------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION replylist(character varying)
  RETURNS record AS
$BODY$
DECLARE
        n character varying;
      r RECORD;
BEGIN
n = name from names where id = $1;
FOR r IN select * from mytable where username = n
LOOP
RETURN r;
END LOOP;
RETURN NULL;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
-------------------------------------------------------------------------------------------------------------------

this appears to do what I need, but I am getting lost with the type definitions etc:

ERROR:  cannot assign non-composite value to a record variable
CONTEXT:  PL/pgSQL function "replylist" line 5 at assignment

What I really want to do is use REPLY QUERY, but perhaps it is not supported in the version I am using(?)

any suggestions?

Cheers.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.