current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

I have to write a PL/pgSQL stored procedure which has two inputs.
The first input should go into a select which returns a set of timestamps.

With these returned timestamps I intend to loop over a second select, which takes the second input of the function and returns a single row I have to collect in the iteration of the loop. I then assemble the output of the second select and return that as a result set.

FUNCTION (Input A B)
SELECT FROM T1 WHERE X = A INTO RS1.
LOOP VAL OVER RS1
   Select FROM T2 WHERE Y = B AND Z = VAL INTO RS2
END LOOP
RETURN ASSEMBLED RS2

Can somebody point me to a working skeleton on which I can work on, btw. what are the relevant parts I need from the documentation? I think I need:

  • RETURNS TABLE () AS to declare the result set

  • A CURSOR to loop over the second query, using the results of the first query

share|improve this question
3  
Why would you do this in a function when you can readily do it with a simple query using join? – Gordon Linoff Jan 30 at 14:38
    
Well the problem is that one select uses a <= in the when clause, which prevents me from using an IN. I do not how to do that otherwise – JohnDoe Jan 30 at 15:01
    
JOIN is not really related to IN -- you can read more about the concept here – pozs Jan 30 at 15:21

2 Answers 2

up vote 0 down vote

Just try to use Common Table Expressions instead of trying to iterate explicitly over rows using pgpsql code :

with T1_vals as (
  select val from T1 where X = A
), T2_vals as (
  select T2.val from T2, T1_vals where T2.Z = T1_vals.val and T2.Y = B
) select val from T2_vals;

As a general rule : be careful when you're tempted to iterate explicitly over values, as SQL offers generally simplier solutions with much better performance.

share|improve this answer
up vote 0 down vote

You don't need a cursor, a nested function call or a loop.
You don't need a CTE, either.

Use a very basic query with a JOIN:

SELECT t2.*
FROM   t1
JOIN   t2 ON t2.z = t1.val 
WHERE  t1.x = _input_a
AND    t2.y = _input_b;

Set-based solutions are typically superior to a procedural approach in SQL. It's a rare occasion that looping is the best solution.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.