pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/PgSQL: stmt_fors and variable value after loop
6+ messages / 2 participants
[nested] [flat]
* PL/PgSQL: stmt_fors and variable value after loop
@ 2015-01-19 15:37 Marko Tiikkaja <marko@joh.to>
0 siblings, 1 reply; 6+ messages in thread
From: Marko Tiikkaja @ 2015-01-19 15:37 UTC (permalink / raw)
To: pgsql-docs
Hi,
As far as I can tell, according to the documentation[1] the return value
of this PL/PgSQL function is undefined:
CREATE OR REPLACE FUNCTION public.foof()
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare
_f1 int;
begin
for _f1 in select 1 loop
end loop;
return _f1;
end
$function$
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?
.marko
[1]:
http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/PgSQL: stmt_fors and variable value after loop
@ 2015-01-19 15:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marko Tiikkaja <marko@joh.to>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2015-01-19 15:44 UTC (permalink / raw)
To: Marko Tiikkaja <marko@joh.to>; +Cc: pgsql-docs
Marko Tiikkaja <marko@joh.to> writes:
> As far as I can tell, according to the documentation[1] the return value
> of this PL/PgSQL function is undefined:
> CREATE OR REPLACE FUNCTION public.foof()
> RETURNS integer
> LANGUAGE plpgsql
> AS $function$
> declare
> _f1 int;
> begin
> for _f1 in select 1 loop
> end loop;
> return _f1;
> end
> $function$
> 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? The above certainly isn't one.
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/PgSQL: stmt_fors and variable value after loop
@ 2015-01-19 16:17 Marko Tiikkaja <marko@joh.to>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Marko Tiikkaja @ 2015-01-19 16:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/PgSQL: stmt_fors and variable value after loop
@ 2015-01-19 16:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marko Tiikkaja <marko@joh.to>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2015-01-19 16:24 UTC (permalink / raw)
To: Marko Tiikkaja <marko@joh.to>; +Cc: pgsql-docs
Marko Tiikkaja <marko@joh.to> writes:
> On 1/19/15 4:44 PM, Tom Lane wrote:
>> 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:
> ...
> 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.
I would have said that (4) would be the natural thing. What have you
got against it?
regards, tom lane
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/PgSQL: stmt_fors and variable value after loop
@ 2015-01-19 16:33 Marko Tiikkaja <marko@joh.to>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Marko Tiikkaja @ 2015-01-19 16:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
On 1/19/15 5:24 PM, Tom Lane wrote:
> Marko Tiikkaja <marko@joh.to> writes:
>> What I'm doing would look something like this:
>> ...
>> 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.
>
> I would have said that (4) would be the natural thing. What have you
> got against it?
The balance is in a string meant for human consumption, so I'd have to
not only dig into the JSON but also extract the relevant portion of that
string using string manipulation. Even worse, in the future these
values might be encrypted, so option #4 isn't even available.
.marko
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PL/PgSQL: stmt_fors and variable value after loop
@ 2015-01-19 17:10 Marko Tiikkaja <marko@joh.to>
parent: Marko Tiikkaja <marko@joh.to>
1 sibling, 0 replies; 6+ messages in thread
From: Marko Tiikkaja @ 2015-01-19 17:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
FWIW, I went with this method:
On 1/19/15 5:17 PM, I wrote:
> 3) Use a count(*) OVER () inside the query and EXIT if that count is 1
I guess defining this behavior wouldn't be such a big win, since the
approach I showed would only work if the loop body keeps a count of the
number of iterations somewhere; FOR doesn't appear to be setting
row_count. I'm having a hard time imagining actual use cases where the
count doesn't matter but you'd like to look at the last values anyway.
If you think there's any chance that this would prevent us from changing
the implementation for the better in the future, I think we should just
drop this issue.
.marko
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-01-19 17:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-01-19 15:37 PL/PgSQL: stmt_fors and variable value after loop Marko Tiikkaja <marko@joh.to>
2015-01-19 15:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-01-19 16:17 ` Marko Tiikkaja <marko@joh.to>
2015-01-19 16:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-01-19 16:33 ` Marko Tiikkaja <marko@joh.to>
2015-01-19 17:10 ` Marko Tiikkaja <marko@joh.to>
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