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@postgresql.org