Re: Calling plSQL functions - Mailing list pgsql-sql

Thanks for the code snippet Josh,

I'll give this method a try as it is only for a simple listing and will not
have to do too many things special.

Cheers
Lonnie

--- Josh Berkus <josh@agliodbs.com> wrote:
> 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      josh@agliodbs.com
>     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


__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail. 
http://personal.mail.yahoo.com/