?
Solved

use query result in a second query

Posted on 2008-11-03
5
Medium Priority
?
990 Views
Last Modified: 2013-11-05
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.
0
Comment
Question by:meverest
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:SreejithG
ID: 22873515
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
 
LVL 37

Author Comment

by:meverest
ID: 22873676
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
 
LVL 3

Expert Comment

by:SreejithG
ID: 22873717
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
 
LVL 37

Author Comment

by:meverest
ID: 22873746
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
 
LVL 37

Accepted Solution

by:
meverest earned 0 total points
ID: 22874034
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question