Hi Frank Thanks for the info - I think I mnight just have to join pgsql-general for this (it'd probs be useful for other stuff too...) about the optimizer - that seems to have triggered a memory about the optimiser also optimising functions where the same parameters give the same result... hmmm. Thanks -- Shane On Tuesday 05 Feb 2002 3:23 am, Frank Bax wrote: > I'm not sure; but I vaguely remember reading in pgsql-general that the > optimizer can tell if the function returns a constant, so only evaluate it > once. Yup, there it is: > http://archives.postgresql.org/pgsql-general/2001-03/msg01476.php > If you stick with one function per constant, it should still be fast. You > loose that if you go the table route, though; or even a case-type statement > within function. > > Further details (and confirmation of statement about speed of 'constant' > functions) might be obtained by posting to pgsql-general. > > Frank > > At 09:33 PM 2/4/02 +0000, Shane Wright wrote: > >Hi Frank, > > > >Thanks, interesting idea - it looks like it would work fine, but wouldn't > > it be a little on the slow side? > > > >I like the constant table idea (hmm, could automatically populate the > >constant table by parsing my PHP definitions files, tasty...) - but again > >speed is of the essence... > > > >-- > >Shane > > > >On Monday 04 Feb 2002 9:12 pm, Frank Bax wrote: > >> Try a pg function. > >> > >> http://www.postgresql.org/idocs/index.php?sql-createfunction.html > >> > >> CREATE FUNCTION one() RETURNS int4 > >> AS 'SELECT 1 AS RESULT' > >> LANGUAGE 'sql'; > >> SELECT one() AS answer; > >> answer > >> -------- > >> 1 > >> > >> If you are truly adventurous, you could create a 'constant' table and > >> have the function take an argument, which is key to the table. > >> > >> Frank > >> > >> At 02:00 PM 2/4/02 +0000, Shane Wright wrote: > >> >Hi > >> > > >> >This may be the wrong list to ask this, apologies if so :) > >> > > >> >Anyway, is there any way to define constants in PostgreSQL - my app has > >> > lots of them at the PHP level, but it'd be nice to use them directly > >> > in the SQL without overly complex query building. > >> > > >> >Something like this... > >> > > >> >DEFINE CONSTANT MYCONST = 4; > >> > > >> >SELECT * FROM mytable WHERE myfield=MYCONST; > >> > > >> > > >> >Thanks > >> > > >> >-- > >> >Shane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
