From: Marko Tiikkaja <marko@joh.to>
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
Date: Mon, 19 Jan 2015 17:17:09 +0100
Message-ID: <54BD2E05.2090004@joh.to> (raw)
In-Reply-To: <26441.1421682246@sss.pgh.pa.us>
References: <54BD24C1.4060601@joh.to>
<26441.1421682246@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
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
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 using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: marko@joh.to, tgl@sss.pgh.pa.us
Subject: Re: PL/PgSQL: stmt_fors and variable value after loop
In-Reply-To: <54BD2E05.2090004@joh.to>
* 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