[BUGS] Function is called multiple times in subselect

[Chris Campbell]

pgsql-bugs:

I wrote a plpgsql function that does a fairly expensive calculation on its input, and want to peform a query that:

1. Selects certain rows from a table, then
2. Calls my function on the selected rows

So I wrote a query and used a subselect to first select the rows, and then used the outer select to call my function on each of the selected rows and return the result.

I referenced the result of my inner select's calculation multiple times in the outer select, and found that my function is called once for *each reference* to its result in the outer select, rather than once for each row of the inner select.

Here's a simple example:

    CREATE FUNCTION square_it(INTEGER) RETURNS INTEGER AS '
    DECLARE
       i ALIAS FOR $1;
    BEGIN
       RAISE NOTICE ''square_it(%)'', i;
       RETURN (i * i);
    END;
    ' LANGUAGE 'plpgsql';

CREATE TABLE foo (i INTEGER);

    INSERT INTO foo (i) VALUES (1);
    INSERT INTO foo (i) VALUES (2);
    INSERT INTO foo (i) VALUES (3);
    INSERT INTO foo (i) VALUES (4);

    SELECT query.i,
           query.squared AS test1,
           query.squared + 1 AS test2,
           query.squared + 2 AS test3,
           query.squared + 3 AS test4
    FROM (
        SELECT i,
               square_it(i) AS squared
        FROM foo
    ) query;

When I run it, I expect to see 4 lines of output, and I expect that square_it() will have been called 4 times (once for each row). However, it is actually called *4 times for each row* because I reference "query.squared" 4 times in the outer select.

    NOTICE:  square_it(1)
    NOTICE:  square_it(1)
    NOTICE:  square_it(1)
    NOTICE:  square_it(1)
    NOTICE:  square_it(2)
    NOTICE:  square_it(2)
    NOTICE:  square_it(2)
    NOTICE:  square_it(2)
    NOTICE:  square_it(3)
    NOTICE:  square_it(3)
    NOTICE:  square_it(3)
    NOTICE:  square_it(3)
    NOTICE:  square_it(4)
    NOTICE:  square_it(4)
    NOTICE:  square_it(4)
    NOTICE:  square_it(4)
     i | test1 | test2 | test3 | test4
    ---+-------+-------+-------+-------
     1 |     1 |     2 |     3 |     4
     2 |     4 |     5 |     6 |     7
     3 |     9 |    10 |    11 |    12
     4 |    16 |    17 |    18 |    19
    (4 rows)

I don't think this should be happening (PostgreSQL 7.4.1). I think it should be saving the result of the calculation in the resulting rows from the innery query. In my case, that means my query takes 4 times longer than it should. And when it's a query that takes a nontrivial amount of time to execute, that's harsh. Any ideas?

Here's the query plan:

QUERY PLAN

-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..37.50 rows=1000 width=4) (actual time=3.203..4.384 rows=4 loops=1)
Total runtime: 4.742 ms

Thanks!

- Chris

smime.p7s

Description: S/MIME Cryptographic Signature