Re: Calling plSQL functions

Lonnie, Tom:

Here's a somewhat complicated example of what Tom's talking about from
my own programs.  

HOWEVER, you should use this kind of structure sparingly, if at all. 
SQL is a declarative language, rather than a procedural one.  For
updates to groups of records, you should put the updates in sets and use
declarative statements whenever possible, rather than a looping
structure.

If you find you *have* to do a lot of looping rather than taking a
declarative approach, you might want to consider changing languages. 
PL/perl, PL/TCL and C are all much better equipped to handle loops and
arrays than PL/pgSQL and SQL.

The example, part of a 279-line function which calculates a weighted job
score evaluation for an HR database:

--Calculate DETAILS modifier
--loop through details one at a time, adding to candidates 
--that have that detail
       count_odetails := count_details - COALESCE(count_rdetails, 0);
       IF count_odetails > 0 THEN               insert_loop := 0;               WHILE insert_loop < count_odetails
LOOP
                       SELECT detail_id INTO detail_no                       FROM order_details
WHEREorder_usq = v_order AND detail_req = FALSE                       ORDER BY detail_id                       LIMIT 1
OFFSETinsert_loop;
 
                       insert_loop := insert_loop + 1;
                       UPDATE matches SET match_score = match_score +
(20::NUMERIC/CAST(count_detailsAS
 
NUMERIC))                       FROM candidate_details                       WHERE candidate_details.candidate_usq =
matches.candidate_usq                               AND match_sq = match_id                               AND detail_id
=detail_no;
 
               END LOOP;       END IF;

(NOTE:  OFFSET will not accept any math if set dynamically {as above}. 
Thus, "LIMIT 1 OFFSET insert_loop + 1" will error).

-Josh

-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 565-7293  for law firms, small
businesses      fax  621-2533   and non-profit organizations.       San Francisco