Thread: stable function called for every row?

Hello,

I have a query like this:

SELECT * FROM table WHERE has_permission('permission_name');

and the function

CREATE FUNCTION has_permission(IN pname text, OUT is_ok boolean) RETURNING boolean AS
$BODY$
BEGIN
  SELECT has_perm INTO is_ok FROM permission WHERE title = pname;

  RAISE INFO 'function called';

  IF is_ok IS FALSE THEN
    -- this function is VOLATILE
    PERFORM log.exception('permission denied');
  END IF;

  RETURN;
END
$BODY$ LANGUAGE 'plpgsql' STABLE SECURITY DEFINER;

The function is called for every row in the table if it is stable or
volatile and only once if it is immutable?

I thought it had to be called only once, if it is stable.

(PostgreSQL 8.3.5)

Regards,
 Gerhard

I isolated my problem a little bit:

CREATE FUNCTION get_array() RETURNS integer[] AS
$BODY$
BEGIN
  RAISE INFO 'get_array';
  RETURN ARRAY[1, 2];
END
$BODY$ LANGUAGE 'plpgsql' STABLE;

And now

SELECT * FROM generate_series(1,3) a(b) where array[b] <@ core.get_array();

gives me:

INFO:  get_array
INFO:  get_array
INFO:  get_array
 b
---
 1
 2
(2 rows)

Why?? Wlli functions which returns an array not be cached?

Regards,
  Gerhard

Hello,

stable, volatile, immutable flag doesn't necessary means caching or
not caching.

if you need really only one call, use srf function

postgres=# create table foo(a int);
CREATE TABLE
postgres=# insert into foo values(10),(20);
INSERT 0 2
postgres=# create function foof(bool) returns setof int as $$begin
raise notice 'foof call'; if $1 then return next 1; else return next
0; end if; return; end;$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from foof(true);
NOTICE:  foof call
 foof
------
    1
(1 row)


postgres=# select * from foo,foof(true);
NOTICE:  foof call
 a  | foof
----+------
 10 |    1
 20 |    1
(2 rows)

regards
Pavel Stehule

2009/1/7 Gerhard Heift <ml-postgresql-20081012-3518@gheift.de>:
> I isolated my problem a little bit:
>
> CREATE FUNCTION get_array() RETURNS integer[] AS
> $BODY$
> BEGIN
>  RAISE INFO 'get_array';
>  RETURN ARRAY[1, 2];
> END
> $BODY$ LANGUAGE 'plpgsql' STABLE;
>
> And now
>
> SELECT * FROM generate_series(1,3) a(b) where array[b] <@ core.get_array();
>
> gives me:
>
> INFO:  get_array
> INFO:  get_array
> INFO:  get_array
>  b
> ---
>  1
>  2
> (2 rows)
>
> Why?? Wlli functions which returns an array not be cached?
>
> Regards,
>  Gerhard
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFJZQ2Ea8fhU24j2fkRAlChAKCDTbhPdKxschTqScfhqRb5olvQ5wCcCcgl
> iMUlTPHTmX0jX/G84Pk82iA=
> =b/pY
> -----END PGP SIGNATURE-----
>
>

Gerhard Heift <ml-postgresql-20081012-3518@gheift.de> writes:
> The function is called for every row in the table if it is stable or
> volatile and only once if it is immutable?

Yes, possibly.

> I thought it had to be called only once, if it is stable.

No.  Stable means that it is *okay* to call it only once per query,
not that that is *guaranteed* to happen.  (What it really does is give
the planner license to use an indexscan on a condition involving the
function --- an indexscan's comparison value is evaluated just once,
so it would give the wrong answers for a non-stable function.)

            regards, tom lane