On Fri, Dec 21, 2012 at 10:45 AM, Christian Hammers <c...@lathspell.de> wrote:
> 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...) > Good, ingenious way to make crazy a DBA ;) Thanks again! > bye, > > -christian- > > > On Fri, 21 Dec 2012 10:31:43 -0200 > Alex Pires de Camargo <acama...@gmail.com> 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 <c...@lathspell.de> > wrote: > > > > > Hello > > > > > > On Fri, 21 Dec 2012 08:46:14 -0200 > > > Alex Pires de Camargo <acama...@gmail.com> 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. > -- Alex acama...@gmail.com "Por que, no mundo, os maus, tão frequentemente, sobrepujam os bons em influência? -Pela fraqueza dos bons; Os maus são intrigantes e audaciosos, os bons são tímidos. Quando estes o quiserem, dominarão." -- Livro dos Espíritos, Q932. http://livrodosespiritos.wordpress.com/
