Message-ID: <54BD2E05.2090004@joh.to>
Date: Mon, 19 Jan 2015 17:17:09 +0100
From: Marko Tiikkaja <marko@joh.to>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: PG Docs <pgsql-docs@postgresql.org>
Subject: Re: PL/PgSQL: stmt_fors and variable value after loop
References: <54BD24C1.4060601@joh.to> <26441.1421682246@sss.pgh.pa.us>
In-Reply-To: <26441.1421682246@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

On 1/19/15 4:44 PM, Tom Lane wrote:
> Marko Tiikkaja <marko@joh.to> writes:
>> But at least based on my understanding of exec_for_query() in pl_exec.c,
>> a FOR loop over query results will always terminate with the values from
>> the last row if at least one was found, regardless of whether EXIT was
>> used or not.  Is there a reason this detail has been left undefined in
>> the documentation?  If not, can we change the documentation?
>
> It seems to me that to do so would mostly be to encourage sloppy
> programming practices, at the price of constraining future implementation
> changes.  Can you give a compelling example of a non-kluge usage for
> such an assumption?

What I'm doing would look something like this:

CREATE FUNCTION parse_response(_response json) RETURNS void AS $$
DECLARE
_AvailableAccounts json;
_Currency text;
_Balance text;
BEGIN

FOR _Currency, _Balance IN
   <complex query to figure out which currencies and amounts are in the 
response>
LOOP
     _AvailableAccounts := json_push(_AvailableAccounts,
         <complex json structure here formed from _Currency and _Balance>
     );
END LOOP;

IF json_array_length(_AvailableAccounts) > 1 THEN
    -- use _AvailableAccounts
ELSIF json_array_length(_AvailableAccounts) = 1 THEN
    -- use _Balance and _Currency
ELSE
    RAISE EXCEPTION 'oops';
END IF;

END
$$ LANGUAGE plpgsql;


Available workarounds right now would be, as far as I can tell:

   1) Have separate variables which I assign to inside the loop, and use 
those in case the loop found exactly one row
   2) Aggregate the (currency, balance) pairs into an array first, and 
decide based on the array's cardinality what to do
   3) Use a count(*) OVER () inside the query and EXIT if that count is 1
   4) Extract the values back from the sole element in the 
_AvailableAccounts array

I don't particularly like any of those.


.marko


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs