Обсуждение: Stored Procedure Assistance

I'm relatively new to stored procedures and I've run into a situation that is
above my current level of ability and I can't seem to find any examples of
how to go about it.

Here is the run down of what I need to do, if someone could either give me an
exmaple or even a nice repository of information on how to do this, I would
be quite grateful.

I'm looking at a series of queries, all simple selects, that are run to
produce a final result set.  Right now they are coded in php and there are
some techniques used to construct the queries that I'm trying to duplicate on
the backend.

The first query is simple:

SELECT a FROM table_a WHERE column_a = b

This will return a single row.  The next query takes that derived value and
does a simliar select that returns multiple rows.

SELECT c FROM table_b WHERE column_b = a

The next query has some logic in php that constructs it.  Basically if c > 0
than the results of query 2 are setup as ORs in the WHERE clause.

SELECT d FROM table_c WHERE column_c = c1 OR column_c = c2, etc.

The first two queries are not a problem, but how can I take the array of
results from query 2 and put them into query 3?

Thanks in advance.

brad
--
Bradley J. Bartram

On Tue, Jul 08, 2003 at 12:55:00PM -0400, Bradley J. Bartram wrote:

> SELECT c FROM table_b WHERE column_b = a
>
> The next query has some logic in php that constructs it.  Basically if c > 0
> than the results of query 2 are setup as ORs in the WHERE clause.
>
> SELECT d FROM table_c WHERE column_c = c1 OR column_c = c2, etc.
>
> The first two queries are not a problem, but how can I take the array of
> results from query 2 and put them into query 3?

Huh... why don't you do a

SELECT d FROM table_c WHERE column_c IN (SELECT c FROM table_b WHERE column_b = a)
(or convert to EXISTS for performance)

Another approach could be to build a string, say

string = ''SELECT d FROM table_c WHERE '';
FOR blah IN SELECT-values-for-c LOOP
    string = string || ''OR column_c = '' || blah
END LOOP;
EXECUTE string;

(adjust as needed, exclude the OR in the first iteration, etc)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Los romanticos son seres que mueren de deseos de vida"

On Tue, 8 Jul 2003, Bradley J. Bartram wrote:

> The first query is simple:
>
> SELECT a FROM table_a WHERE column_a = b
>
> This will return a single row.  The next query takes that derived value
and
> does a simliar select that returns multiple rows.
>
> SELECT c FROM table_b WHERE column_b = a
>
> The next query has some logic in php that constructs it.  Basically if c
> 0
> than the results of query 2 are setup as ORs in the WHERE clause.
>
> SELECT d FROM table_c WHERE column_c = c1 OR column_c = c2, etc.
>
> The first two queries are not a problem, but how can I take the array of
> results from query 2 and put them into query 3?

Are you sure you need plpgsql to get from point a to point d?  It looks
like this could be a single query:

select d from table_c where column_c in
 (select c from table_a, table_b where
  table_a.column_a = b and
  table_b.column_b = table_a.a)

michael

On Tuesday 08 Jul 2003 5:55 pm, Bradley J. Bartram wrote:
> The first query is simple:
>
> SELECT a FROM table_a WHERE column_a = b
>
> This will return a single row.  The next query takes that derived value and
> does a simliar select that returns multiple rows.
>
> SELECT c FROM table_b WHERE column_b = a
>
> The next query has some logic in php that constructs it.  Basically if c >
> 0 than the results of query 2 are setup as ORs in the WHERE clause.
>
> SELECT d FROM table_c WHERE column_c = c1 OR column_c = c2, etc.
>
> The first two queries are not a problem, but how can I take the array of
> results from query 2 and put them into query 3?

Well, we can combine (1) and (2):

SELECT c FROM table_b, table_a
WHERE table_b.column_b = table_a.a
AND table_a.column_a = b;

The third needs to be something like:

SELECT d FROM table_c WHERE column_c IN (...the combined select above...)

So long as you don't have too many results this should work fine - no need for
a stored procedure at all.

If I've understood your problem, that should work.
--
  Richard Huxton