Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3DKkq821473 for ; Fri, 13 Apr 2001 16:46:52 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [207.172.28.154] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4.1) with ESMTP id 37766; Fri, 13 Apr 2001 13:46:49 -0700 Message-ID: <3AD7657D.87AFA73D@agliodbs.com> Date: Fri, 13 Apr 2001 13:45:49 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: Lonnie Cumberland CC: Tom Lane , pgsql-sql@postgresql.org Subject: Re: Calling plSQL functions References: <20010413185714.7788.qmail@web12502.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/209 X-Sequence-Number: 2559 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