On 10/08/2015 01:57 AM, Oleksii Kliukin wrote:
On 06 Oct 2015, at 23:31, Tom Lane <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:
Oleksii Kliukin <alexk@xxxxxxxxxxxx <mailto:alexk@xxxxxxxxxxxx>> writes:
This should work, but I'm interested in finding out why the original
statement behaves the way I’ve described.
plpgsql's SELECT INTO is only capable of storing a single result row,
so it only executes the statement far enough to obtain one row, and
then stops (as though a LIMIT were present). There is no guarantee
about how much useless computation will get done underneath.
Thank you, now it’s clear. I have to say there is no guarantee that the
computation would be useless. Someone might be calling a function that
updates/deletes rows in the SELECT INTO block, being forced to use
SELECT INTO by inability of pl/pgSQL to just discard the result of a
normal SELECT. I know one can use a loop or call PERFORM, but in some
cases (a complex CTE computing the data for the function being called at
the end, which updates the tables with this data) actually using SELECT
INTO looks like the easiest path to achieve the desired result.
Well the best I can come up with at the moment is:
DO $$
DECLARE l_id integer;
BEGIN
WITH gs AS (select generate_series(1,10) as id)
SELECT test(id) FROM gs ORDER BY id INTO l_id;
END;
$$ LANGUAGE plpgsql;
This is essentially the same catch as with LIMIT, but LIMIT is better
documented :-)
If this is not the behavior you want, you shouldn't be using SELECT INTO
(which, I'll note, is very clearly documented as meant only for single-row
results).
This is true, but what if I don’t care about the result and cannot use
PERFORM?
I admit it is a rather corner case, but to me it’s not clear from the
documentation that SELECT INTO will not try to compute more rows than
necessary. The docs say "Any result rows after the first row are
discarded”, it’s not clear from it whether those rows are supposed to be
evaluated before they are discarded, hence, the question that started
this thread.
A plausible alternative is a FOR IN SELECT loop, which would
have the benefit that you could actually do something with the row values.
Agree on that.
Kind regards,
--
Oleksii
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
