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>:
Show quoted text
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