Hallo A function that is used as part of an index has at least to be declared immutable: devel_np=# CREATE OR REPLACE FUNCTION f() RETURNS int AS $$ BEGIN return (random()*100)::int; END; $$ VOLATILE LANGUAGE plpgsql; CREATE FUNCTION devel_np=# CREATE INDEX ON t (f(), i); ERROR: functions in index expression must be marked IMMUTABLE Of couse, you can just declare your function as "IMMUTABLE" and still call random() or access other tables in it if you think you know what you're doing. (I wonder if it's somehow possible to get PostgreSQL into an endless loop or crash by doing an ORDER BY which uses an index that returns random values...) bye, -christian- On Fri, 21 Dec 2012 10:31:43 -0200 Alex Pires de Camargo <acamargo@xxxxxxxxx> wrote: > Thanks a lot! > > From documentation: > > " > IMMUTABLE indicates that the function cannot modify the database and always > returns the same result when given the same argument values; that is, it > does not do database lookups or otherwise use information not directly > present in its argument list. If this option is given, any call of the > function with all-constant arguments can be immediately replaced with the > function value. > " > > I understand that to be immutable a function should not access mutable data. > > If my function access another table that I have guarantee that it will not > be changed, It's safe to turn that function immutable and use in an index? > I know that i'll be punished if my guarantee fails... > > Regards, > > > On Fri, Dec 21, 2012 at 10:12 AM, Christian Hammers <ch@xxxxxxxxxxxx> wrote: > > > Hello > > > > On Fri, 21 Dec 2012 08:46:14 -0200 > > Alex Pires de Camargo <acamargo@xxxxxxxxx> wrote: > > > > > Is it possible? > > > > > > Thanks! > > > > Why not? > > > > devel_np=# CREATE TABLE t (i int); > > CREATE TABLE > > > > devel_np=# CREATE INDEX ON t (length(i::text), i); > > CREATE INDEX > > > > bye, > > > > -christian- > > > > > -- Network Engineering & Design; Content Delivery Platform & IP NETCOLOGNE Gesellschaft für Telekommunikation mbH Am Coloneum 9 | 50829 Köln Tel: 0221 2222-8711 | Fax: 0221 2222-78711 www.netcologne.de Geschäftsführer: Dr. Hans Konle (Sprecher) Dipl.-Ing. Karl-Heinz Zankel HRB 25580, AG Köln Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese Nachricht versehentlich erhalten haben, bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen. Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer Weise verwendet werden. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
