pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Lonnie Cumberland <lonnie_cumberland@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calling plSQL functions
Date: Fri, 13 Apr 2001 13:45:49 -0700
Message-ID: <3AD7657D.87AFA73D@agliodbs.com> (raw)
References: <20010413185714.7788.qmail@web12502.mail.yahoo.com>

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



view thread (10+ messages)  latest in thread

Message-ID: <3AD7657D.87AFA73D@agliodbs.com>
Permalink:  ../3AD7657D.87AFA73D@agliodbs.com/
Also on:    postgresql.org/message-id/3AD7657D.87AFA73D@agliodbs.com

 · 

reply

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: josh@agliodbs.com, lonnie_cumberland@yahoo.com, tgl@sss.pgh.pa.us
  Subject: Re: Calling plSQL functions
  In-Reply-To: <3AD7657D.87AFA73D@agliodbs.com>
  References: <20010413185714.7788.qmail@web12502.mail.yahoo.com> <3AD7657D.87AFA73D@agliodbs.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