Обсуждение: PL/PgSQL: stmt_fors and variable value after loop
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
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
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
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
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
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
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
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
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
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
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