From: Lonnie Cumberland <lonnie_cumberland@yahoo.com>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Calling plSQL functions
Date: Fri, 13 Apr 2001 14:07:15 -0700 (PDT)
Message-ID: <20010413210715.26761.qmail@web12502.mail.yahoo.com> (raw)
In-Reply-To: <3AD7657D.87AFA73D@agliodbs.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: lonnie_cumberland@yahoo.com, josh@agliodbs.com
Subject: Re: Calling plSQL functions
In-Reply-To: <20010413210715.26761.qmail@web12502.mail.yahoo.com>
References: <3AD7657D.87AFA73D@agliodbs.com> <20010413210715.26761.qmail@web12502.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox