Postgres Pro
Downloads
Home   >   mailing lists

Thread: constants

constants

From
Shane Wright
Date:
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

Re: constants

From
Chadwick Rolfs
Date:
There is a define:

define("MYCONST", "4");

read here...
http://www.php.net/manual/en/language.constants.php

and here...
http://www.php.net/manual/en/function.define.php

and anywhere else I might have missed.  I haven't used postgresql to
define constants, yet, but I do believe it's possible...

On Mon, 4 Feb 2002, 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
>

Chadwick Rolfs - cmr@gis.net
Cleveland State University - Student
Music Major - The Holden Arboretum Volunteer
Computer Programmer - Student Employee
--*I finally found powdered water;
I just can't figure out what to add to it*--

Re: constants

From
Frank Bax
Date:
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
>

Re: constants

From
Shane Wright
Date:
Thanks but I know how to do it in PHP - the problem is that I want to do it
straight in SQL to avoid inserting my PHP constants into my SQL queries.

--
Shane

On Monday 04 Feb 2002 7:48 pm, Chadwick Rolfs wrote:
> There is a define:
>
> define("MYCONST", "4");
>
> read here...
> http://www.php.net/manual/en/language.constants.php
>
> and here...
> http://www.php.net/manual/en/function.define.php
>
> and anywhere else I might have missed.  I haven't used postgresql to
> define constants, yet, but I do believe it's possible...
>
> On Mon, 4 Feb 2002, 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
>
> Chadwick Rolfs - cmr@gis.net
> Cleveland State University - Student
> Music Major - The Holden Arboretum Volunteer
> Computer Programmer - Student Employee
> --*I finally found powdered water;
> I just can't figure out what to add to it*--
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: constants

From
Shane Wright
Date:
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

Re: constants

From
Frank Bax
Date:
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

Re: constants

From
Shane Wright
Date:
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