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
WHERE order_usq = v_order AND detail_req = FALSE
ORDER BY detail_id
LIMIT 1 OFFSET insert_loop;
insert_loop := insert_loop + 1;
UPDATE matches SET match_score = match_score +
(20::NUMERIC/CAST(count_details AS
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 information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
