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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
meverestAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.