Обсуждение: 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