pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedconstants
7+ messages / 3 participants
[nested] [flat]
* constants
@ 2002-02-04 14:00 Shane Wright <me@shanewright.co.uk>
0 siblings, 2 replies; 7+ messages in thread
From: Shane Wright @ 2002-02-04 14:00 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: constants
@ 2002-02-04 19:48 Chadwick Rolfs <cmr@shell.gis.net>
parent: Shane Wright <me@shanewright.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: Chadwick Rolfs @ 2002-02-04 19:48 UTC (permalink / raw)
To: Shane Wright <me@shanewright.co.uk>; +Cc: pgsql-php
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*--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: constants
@ 2002-02-04 21:12 Frank Bax <fbax@sympatico.ca>
parent: Shane Wright <me@shanewright.co.uk>
1 sibling, 2 replies; 7+ messages in thread
From: Frank Bax @ 2002-02-04 21:12 UTC (permalink / raw)
To: me@shanewright.co.uk; +Cc: pgsql-php
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: constants
@ 2002-02-04 21:31 Shane Wright <me@shanewright.co.uk>
parent: Chadwick Rolfs <cmr@shell.gis.net>
0 siblings, 0 replies; 7+ messages in thread
From: Shane Wright @ 2002-02-04 21:31 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: constants
@ 2002-02-04 21:33 Shane Wright <me@shanewright.co.uk>
parent: Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 7+ messages in thread
From: Shane Wright @ 2002-02-04 21:33 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: constants
@ 2002-02-05 03:23 Frank Bax <fbax@sympatico.ca>
parent: Frank Bax <fbax@sympatico.ca>
1 sibling, 1 reply; 7+ messages in thread
From: Frank Bax @ 2002-02-05 03:23 UTC (permalink / raw)
To: me@shanewright.co.uk; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: constants
@ 2002-02-05 12:59 Shane Wright <me@shanewright.co.uk>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 7+ messages in thread
From: Shane Wright @ 2002-02-05 12:59 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-02-05 12:59 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-04 14:00 constants Shane Wright <me@shanewright.co.uk>
2002-02-04 19:48 ` Chadwick Rolfs <cmr@shell.gis.net>
2002-02-04 21:31 ` Shane Wright <me@shanewright.co.uk>
2002-02-04 21:12 ` Frank Bax <fbax@sympatico.ca>
2002-02-04 21:33 ` Shane Wright <me@shanewright.co.uk>
2002-02-05 03:23 ` Frank Bax <fbax@sympatico.ca>
2002-02-05 12:59 ` Shane Wright <me@shanewright.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox