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