Re: [GENERAL] stable function called for every row?
Gerhard Heift 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stable function called for every row?
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 : > 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- > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stable function called for every row?
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 signature.asc Description: Digital signature
[GENERAL] 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 signature.asc Description: Digital signature