Re: dubious optimization of the function in SELECT INTO target list - Mailing list pgsql-general

On 08 Oct 2015, at 16:00, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 10/08/2015 01:57 AM, Oleksii Kliukin wrote:

On 06 Oct 2015, at 23:31, Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

Oleksii Kliukin <alexk@hintbits.com <mailto:alexk@hintbits.com>> 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;