pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfeature request
33+ messages / 14 participants
[nested] [flat]
* feature request
@ 2004-06-21 08:48 sad <sad@bankir.ru>
0 siblings, 0 replies; 33+ messages in thread
From: sad @ 2004-06-21 08:48 UTC (permalink / raw)
To: pgsql-sql
hello
it might be stupid...
sometimes i am starving UPDATE OR INSERT command
thnx
^ permalink raw reply [nested|flat] 33+ messages in thread
* feature request ?
@ 2004-06-23 09:55 sad <sad@bankir.ru>
0 siblings, 2 replies; 33+ messages in thread
From: sad @ 2004-06-23 09:55 UTC (permalink / raw)
To: pgsql-sql
Hello
since BOOL expression has three possible values: TRUE,FALSE,NULL
plpgsql IF control structure should have three alternate blocks:
THEN,ELSE,NULL
shouldn't it ?
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-23 17:12 Josh Berkus <josh@agliodbs.com>
parent: sad <sad@bankir.ru>
1 sibling, 1 reply; 33+ messages in thread
From: Josh Berkus @ 2004-06-23 17:12 UTC (permalink / raw)
To: sad <sad@bankir.ru>; pgsql-sql
Sad,
> since BOOL expression has three possible values: TRUE,FALSE,NULL
> plpgsql IF control structure should have three alternate blocks:
> THEN,ELSE,NULL
>
> shouldn't it ?
No, why?
How would you construct a tri-valued IF/THEN? Doesn't seem too likely to
me, as well as being different from every other programming language in
existance ...
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 04:56 sad <sad@bankir.ru>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-24 04:56 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Thursday 24 June 2004 09:32, Michael Glaesemann wrote:
> On Jun 24, 2004, at 2:12 AM, Josh Berkus wrote:
> > Sad,
> >
> >> since BOOL expression has three possible values: TRUE,FALSE,NULL
> >> plpgsql IF control structure should have three alternate blocks:
> >> THEN,ELSE,NULL
> >>
> >> shouldn't it ?
> >
> > No, why?
> >
> > How would you construct a tri-valued IF/THEN? Doesn't seem too
> > likely to
> > me, as well as being different from every other programming language in
> > existance ...
>
> Creating a new control structure to do handle this seems odd. However,
> one could easily have the same effect using a nested if. Using the
> pl/pgsql ELSIF construct, it's pretty straightforward.
>
> IF foo IS NULL
> THEN ...
> ELSIF foo
> THEN ...
> ELSE ...
> END IF;
here the foo expression woll be executed twice
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 05:01 sad <sad@bankir.ru>
0 siblings, 0 replies; 33+ messages in thread
From: sad @ 2004-06-24 05:01 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 23 June 2004 21:12, you wrote:
> Sad,
>
> > since BOOL expression has three possible values: TRUE,FALSE,NULL
> > plpgsql IF control structure should have three alternate blocks:
> > THEN,ELSE,NULL
> >
> > shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN? Doesn't seem too likely
> to me, as well as being different from every other programming language in
> existance ...
Three valued BOOLEAN is already different "from every other programming
language in existance"
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 05:32 Michael Glaesemann <grzm@myrealbox.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 33+ messages in thread
From: Michael Glaesemann @ 2004-06-24 05:32 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: sad <sad@bankir.ru>; pgsql-sql
On Jun 24, 2004, at 2:12 AM, Josh Berkus wrote:
> Sad,
>
>> since BOOL expression has three possible values: TRUE,FALSE,NULL
>> plpgsql IF control structure should have three alternate blocks:
>> THEN,ELSE,NULL
>>
>> shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN? Doesn't seem too
> likely to
> me, as well as being different from every other programming language in
> existance ...
Creating a new control structure to do handle this seems odd. However,
one could easily have the same effect using a nested if. Using the
pl/pgsql ELSIF construct, it's pretty straightforward.
IF foo IS NULL
THEN ...
ELSIF foo
THEN ...
ELSE ...
END IF;
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 07:04 sad <sad@bankir.ru>
parent: Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 2 replies; 33+ messages in thread
From: sad @ 2004-06-24 07:04 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
> I don't see what your point is. That SQL is wrong ? Or that SQL is not "C"
> ? Or that SQL is not a "programming language" ?
Who said wrong ? who said SQL ?
I thougth _WHY_
the IF control structure has exactly two alternate blocks ?
a BOOLEAN expression has exactly two possible values, that's why !
Well
in plpgsql we have tri-valued BOOL
Every programmer asks "how a NULL value treated?"
Now you treat NULLs as false. That's your point, but why ?
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 07:48 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 33+ messages in thread
From: Gregory S. Williamson @ 2004-06-24 07:48 UTC (permalink / raw)
To: sad <sad@bankir.ru>; pgsql-sql
Programming languages, perhaps, but clearly not uncommon in SQL ... Informix certainly allows a column to be of type boolean but with a value of NULL for given rows (unless precluded by a not-null constraint). Should we question integers, which can be positive, negative, or -- gasp ! -- NULL ?
I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" ? Or that SQL is not a "programming language" ?
"?Que purposa sirve tanto comedia ? Quien inventan tab miseria ?"
Greg Williamson
DBA
GLobeXplorer LLC
-----Original Message-----
From: sad [mailto:sad@bankir.ru]
Sent: Wed 6/23/2004 10:01 PM
To: pgsql-sql@postgresql.org
Cc:
Subject: Re: [SQL] feature request ?
On Wednesday 23 June 2004 21:12, you wrote:
> Sad,
>
> > since BOOL expression has three possible values: TRUE,FALSE,NULL
> > plpgsql IF control structure should have three alternate blocks:
> > THEN,ELSE,NULL
> >
> > shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN? Doesn't seem too likely
> to me, as well as being different from every other programming language in
> existance ...
Three valued BOOLEAN is already different "from every other programming
language in existance"
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 09:39 sad <sad@bankir.ru>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-24 09:39 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
...IF ELSEIF ELSE
it's all clear
but what about unequality of BOOL type possible value set and IF alternatives
set
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 09:43 sad <sad@bankir.ru>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 0 replies; 33+ messages in thread
From: sad @ 2004-06-24 09:43 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thursday 24 June 2004 14:32, Alexander M. Pravking wrote:
> On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote:
> > Now you treat NULLs as false.
>
> Nope. NULL is neither true, nor false. It's "unknown", or "undefined".
>
> fduch=# SELECT 1 WHERE NULL::boolean;
> ?column?
> ----------
> (0 rows)
DAMN !! Alex ! read the thread before answering !
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 10:24 Michael Glaesemann <grzm@myrealbox.com>
parent: sad <sad@bankir.ru>
1 sibling, 1 reply; 33+ messages in thread
From: Michael Glaesemann @ 2004-06-24 10:24 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
On Jun 24, 2004, at 1:49 PM, sad wrote:
> On Thursday 24 June 2004 09:32, Michael Glaesemann wrote:
>> Creating a new control structure to do handle this seems odd. However,
>> one could easily have the same effect using a nested if. Using the
>> pl/pgsql ELSIF construct, it's pretty straightforward.
>>
>> IF foo IS NULL
>> THEN ...
>> ELSIF foo
>> THEN ...
>> ELSE ...
>> END IF;
>
> here the foo expression will be executed twice
You're right, in that you couldn't use this in a CASE expression in
pure SQL, but it would work in a pl/pgsql function, which would execute
the expression once when it is called. Here's a very simple example:
test=# create or replace function foo_3val(boolean)
returns text
language plpgsql as '
declare
foo alias for $1;
begin
if foo is null
then return ''foo is null'';
elsif foo
then return ''foo is true'';
else
return ''foo is false'';
end if;
end;
';
CREATE FUNCTION
test=# create table foo_vals (foo_id serial unique not null, foo_val
boolean);
NOTICE: CREATE TABLE will create implicit sequence
"foo_vals_foo_id_seq" for "serial" column "foo_vals.foo_id"
NOTICE: CREATE TABLE / UNIQUE will create implicit index
"foo_vals_foo_id_key" for table "foo_vals"
CREATE TABLE
test=# insert into foo_vals (foo_val) values (true);
INSERT 5076542 1
test=# insert into foo_vals (foo_val) values (false);
INSERT 5076543 1
test=# insert into foo_vals(foo_id) values(default);
INSERT 5076544 1
test=# select * from foo_vals;
foo_id | foo_val
--------+---------
1 | t
2 | f
3 |
(3 rows)
test=# select foo_id, foo_3val(foo_val) from foo_vals;
foo_id | foo_3val
--------+--------------
1 | foo is true
2 | foo is false
3 | foo is null
(3 rows)
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 10:32 Iain <iain@mst.co.jp>
parent: sad <sad@bankir.ru>
0 siblings, 0 replies; 33+ messages in thread
From: Iain @ 2004-06-24 10:32 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
> > IF foo IS NULL
> > THEN ...
> > ELSIF foo
> > THEN ...
> > ELSE ...
> > END IF;
>
> here the foo expression woll be executed twice
if you can use an immutable or stable function then the overhead would be
minimal as the system knows that it doesn't need to re-evaluate it.
regards
Iain
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 10:32 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: sad <sad@bankir.ru>
1 sibling, 1 reply; 33+ messages in thread
From: Alexander M. Pravking @ 2004-06-24 10:32 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote:
> Now you treat NULLs as false.
Nope. NULL is neither true, nor false. It's "unknown", or "undefined".
fduch=# SELECT 1 WHERE NULL::boolean;
?column?
----------
(0 rows)
fduch=# SELECT 1 WHERE NOT NULL::boolean;
?column?
----------
(0 rows)
So if you care, you SHOULD use IS [NOT] NULL, as Michael Glaesemann
suggested. If you don't want expression to be calculated twice, use a
temporary variable.
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 12:55 sad <sad@bankir.ru>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-24 12:55 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
> If you were to add a NULL block you'd have to deal with things
> like, if you only have a then and else, do you run the else on NULL or do
> you do nothing? If you do nothing, what if you want the null and else to
> be the same, do you add another way to specify that? If you do the else,
> then the else stops making sense since it's sometimes false and sometimes
> not true.
it is only syntax problem.
really we have more than one way to continue execution if one block is skipped
so your are free to define IF's behavior any way, particularly the way it is
defined now.
two-blocks IF is oviously enough to code ANY algorythm
but the three-blocks IF is more adequate to tri-valued BOOL
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 13:24 sad <sad@bankir.ru>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-24 13:24 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
> then lots of currently perfectly correct
> programs break. If they're the same, then ELSE has different meanings
> depending on whether NULL is specified, and that's generally bad from an
> understanding the language standpoint.
i've already thougth on this
new control structure needed
but the name of the IF is perfect %-)
> In addition, either adding a FALSE and NULL or just a NULL still involves
> looking at the rest of the IF semantics to make sure they make sense. How
> do those interact with ELSIF blocks?
that is because we used to two-valued BOOL...
but anyway i see no clear way to generalize ELSEIF.
may i suppose it was wrong to historically define IF as two-blocks control in
plpgsql ?
forget it.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 13:25 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: sad <sad@bankir.ru>
1 sibling, 1 reply; 33+ messages in thread
From: Stephan Szabo @ 2004-06-24 13:25 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thu, 24 Jun 2004, sad wrote:
> > I don't see what your point is. That SQL is wrong ? Or that SQL is not "C"
> > ? Or that SQL is not a "programming language" ?
>
> Who said wrong ? who said SQL ?
>
> I thougth _WHY_
> the IF control structure has exactly two alternate blocks ?
> a BOOLEAN expression has exactly two possible values, that's why !
>
> Well
> in plpgsql we have tri-valued BOOL
>
> Every programmer asks "how a NULL value treated?"
> Now you treat NULLs as false. That's your point, but why ?
It doesn't treat NULLs as false, it treats them as not true.
There's a least surprise issue here, most people expect if's then block to
run when it is true and the else block to run when it is not true (which
is false for most systems and false and NULL for this form of tri-valued
logic.) If you were to add a NULL block you'd have to deal with things
like, if you only have a then and else, do you run the else on NULL or do
you do nothing? If you do nothing, what if you want the null and else to
be the same, do you add another way to specify that? If you do the else,
then the else stops making sense since it's sometimes false and sometimes
not true.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 14:00 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: Stephan Szabo @ 2004-06-24 14:00 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thu, 24 Jun 2004, sad wrote:
> > If you were to add a NULL block you'd have to deal with things
> > like, if you only have a then and else, do you run the else on NULL or do
> > you do nothing? If you do nothing, what if you want the null and else to
> > be the same, do you add another way to specify that? If you do the else,
> > then the else stops making sense since it's sometimes false and sometimes
> > not true.
>
> it is only syntax problem.
> really we have more than one way to continue execution if one block is skipped
> so your are free to define IF's behavior any way, particularly the way it is
> defined now.
So you want to syntactically allow both ELSE and something like
FALSE/NULL? Or perhaps a different structure from IF entirely?
If you don't then you still run into questions like is
IF booleanval THEN
... 1
ELSE
... 2
NULL
... 2
ENDIF
different from
IF booleanval THEN
... 1
ELSE
... 2
ENDIF
because, if they're different, then lots of currently perfectly correct
programs break. If they're the same, then ELSE has different meanings
depending on whether NULL is specified, and that's generally bad from an
understanding the language standpoint.
In addition, either adding a FALSE and NULL or just a NULL still involves
looking at the rest of the IF semantics to make sure they make sense. How
do those interact with ELSIF blocks?
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 14:34 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: Stephan Szabo @ 2004-06-24 14:34 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thu, 24 Jun 2004, sad wrote:
> > then lots of currently perfectly correct
> > programs break. If they're the same, then ELSE has different meanings
> > depending on whether NULL is specified, and that's generally bad from an
> > understanding the language standpoint.
>
> i've already thougth on this
> new control structure needed
> but the name of the IF is perfect %-)
I was thinking that something like Cs switch might work. There's still a
question of the keywords because I don't like reusing case, but maybe
something of the general form:
case foo
is true
is false
is null
The general form looks more like:
case <expression>
[when <expression>] then <statements>
[is (true | false | null)] then <statements>
[else] <statements>
end case
With (unthoughtout) semantics like
The case expression is evaluated once (although the below
describes things in terms of expressions of the case
expression, it is meant to be indicative of the intent
not the actual implementation)
Each when/is/else clause is evaluated in order as follows:
For a when clause, if the value of the case expression is
equal to the value of the when expression the statements
are run and the case is ended.
For an is clause,
IS TRUE: if case expression IS TRUE is true then the
statements are run and the case is ended
IS FALSE: if case expression IS FALSE is true then the
statements are run and the case is ended
IS NULL: if case expression IS NULL is true then the
statements are run and the case is ended
For an else clause, run the statements (since no preceding
condition has succeeded)
This is basically an extension of the syntax and semantics of one
of the case expression. I don't think the above works keyword-wise
possibly, but it might be a reasonable starting point.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 15:51 Michael Glaesemann <grzm@myrealbox.com>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: Michael Glaesemann @ 2004-06-24 15:51 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
On Jun 24, 2004, at 6:39 PM, sad wrote:
> ...IF ELSEIF ELSE
> it's all clear
> but what about unequality of BOOL type possible value set and IF
> alternatives
> set
In my opinion the short answer is NULL is here because of the SQL
standard. The SQL standard does not specify any kind of "IF
alternative" for 3-valued logic afaik. Why should PostgreSQL go beyond
what the standard specifies in this hairy area? Three-valued logic is
something I strive to stay away from to the best of my ability, as it
is far too complicated for my feeble mind.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 17:32 Jaime Casanova <systemguards@yahoo.com>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 33+ messages in thread
From: Jaime Casanova @ 2004-06-24 17:32 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; sad <sad@bankir.ru>; +Cc: pgsql-sql
Hi all,
Tri-valued boolean?? that's not against boolean concept?? i'm not saying that SQL is wrong nor Postgresql has to go beyond standard, i'm just trying to understand this stuff.
Why not disallow the ability of boolean fields to be null?
thanx in advance,
Jaime Casanova
Michael Glaesemann <grzm@myrealbox.com> wrote:
On Jun 24, 2004, at 6:39 PM, sad wrote:
> ...IF ELSEIF ELSE
> it's all clear
> but what about unequality of BOOL type possible value set and IF
> alternatives
> set
In my opinion the short answer is NULL is here because of the SQL
standard. The SQL standard does not specify any kind of "IF
alternative" for 3-valued logic afaik. Why should PostgreSQL go beyond
what the standard specifies in this hairy area? Three-valued logic is
something I strive to stay away from to the best of my ability, as it
is far too complicated for my feeble mind.
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
---------------------------------
Do You Yahoo!?
Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
Visíta Yahoo! Noticias.
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 17:43 Bruno Wolff III <bruno@wolff.to>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 33+ messages in thread
From: Bruno Wolff III @ 2004-06-24 17:43 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: sad <sad@bankir.ru>; Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thu, Jun 24, 2004 at 07:34:18 -0700,
Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> I was thinking that something like Cs switch might work. There's still a
> question of the keywords because I don't like reusing case, but maybe
> something of the general form:
> case foo
> is true
> is false
> is null
There already is a syntax like this. You can do:
CASE boolean_expression
WHEN TRUE THEN whatever
WHEN FALSE THEN whatever
ELSE whatever
END
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 18:24 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 2 replies; 33+ messages in thread
From: Radu-Adrian Popescu @ 2004-06-24 18:24 UTC (permalink / raw)
To: pgsql-sql; +Cc: sad@bankir.ru
Bruno Wolff III wrote:
>
> There already is a syntax like this. You can do:
> CASE boolean_expression
> WHEN TRUE THEN whatever
> WHEN FALSE THEN whatever
> ELSE whatever
> END
>
Besides, sad, there's no such thing as a tri-valued boolean.
You either have a boolean(with a true/false value) or a NULL, which is something
completely different and it's _not_ one of the two _values_ that a boolean
object takes. I think you're looking at this completely wrong. If in Java you
receive a Boolean object instance, will you start claiming that that Java has
tri-valued booleans, and the "if" should account for that ?!
You can think of values in plpgsql as wrapper objects that carry a value and
have a "is_null" flag; I have no idea how they're implemented in PostgreSQL or
in any RDMBS in general but this should do it, at least for a naive implementation.
Peace,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 18:41 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 33+ messages in thread
From: Stephan Szabo @ 2004-06-24 18:41 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: sad <sad@bankir.ru>; Gregory S. Williamson <gsw@globexplorer.com>; pgsql-sql
On Thu, 24 Jun 2004, Bruno Wolff III wrote:
> On Thu, Jun 24, 2004 at 07:34:18 -0700,
> Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> >
> > I was thinking that something like Cs switch might work. There's still a
> > question of the keywords because I don't like reusing case, but maybe
> > something of the general form:
> > case foo
> > is true
> > is false
> > is null
>
> There already is a syntax like this. You can do:
> CASE boolean_expression
> WHEN TRUE THEN whatever
> WHEN FALSE THEN whatever
> ELSE whatever
> END
True, but I think that mostly suffers from similar understandability
problems. :)
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 19:27 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Jaime Casanova <systemguards@yahoo.com>
0 siblings, 0 replies; 33+ messages in thread
From: Andrew Sullivan @ 2004-06-24 19:27 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jun 24, 2004 at 12:32:59PM -0500, Jaime Casanova wrote:
>
> Why not disallow the ability of boolean fields to be null?
Why not do it yourself? That's what the NOT NULL constraint is for.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-24 22:43 Geoffrey <esoteric@3times25.net>
parent: Radu-Adrian Popescu <radu.popescu@aldratech.com>
1 sibling, 0 replies; 33+ messages in thread
From: Geoffrey @ 2004-06-24 22:43 UTC (permalink / raw)
To: pgsql-sql
Very simply, a boolean may have to values: true or false. It's also
possible that it's not been set to anything (NULL).
--
Until later, Geoffrey Registered Linux User #108567
Building secure systems in spite of Microsoft
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 04:16 sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-25 04:16 UTC (permalink / raw)
To: pgsql-sql
> Very simply, a boolean may have to values: true or false. It's also
> possible that it's not been set to anything (NULL).
really ?
what about (13 < NULL)::BOOL
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 05:37 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: Rosser Schwarz @ 2004-06-25 05:37 UTC (permalink / raw)
To: pgsql-sql
On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
> > Very simply, a boolean may have to values: true or false. It's also
> > possible that it's not been set to anything (NULL).
> really ?
> what about (13 < NULL)::BOOL
Per the semantics of NULL, 13 is neither greater than nor less than
NULL. NULL is the *unknown* value; it's impossible to meaningfully
compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
also.
Since no value, including NULL, is in any way definitively comparable
to NULL -- the unknown value -- comparing to NULL results in ...
unknown.
Otherwise known as NULL.
/rls
--
:wq
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 06:20 sad <sad@bankir.ru>
parent: Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-25 06:20 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; pgsql-sql
On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
> On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
> > > Very simply, a boolean may have to values: true or false. It's also
> > > possible that it's not been set to anything (NULL).
> >
> > really ?
> > what about (13 < NULL)::BOOL
>
> Per the semantics of NULL, 13 is neither greater than nor less than
> NULL. NULL is the *unknown* value; it's impossible to meaningfully
> compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
> also.
READ THE THREAD BEFORE ANSWER
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 07:54 sad <sad@bankir.ru>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 33+ messages in thread
From: sad @ 2004-06-25 07:54 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Rosser Schwarz <rosser.schwarz@gmail.com>; pgsql-sql
> sad wrote:
> > On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
> >>On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
> >>>>Very simply, a boolean may have to values: true or false. It's also
> >>>>possible that it's not been set to anything (NULL).
> >>>
> >>>really ?
> >>>what about (13 < NULL)::BOOL
> >>
> >>Per the semantics of NULL, 13 is neither greater than nor less than
> >>NULL. NULL is the *unknown* value; it's impossible to meaningfully
> >>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
> >>also.
> >
> > READ THE THREAD BEFORE ANSWER
>
> WHAT MAKES YOU THINK HE HASN'T?
I had answered to the proposal to PROHIBIT NULL VALUES
> The key point of argument, and where the problem is with your (13 <
> NULL)::BOOL point is this:
IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!!
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 08:33 Richard Huxton <dev@archonet.com>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: Richard Huxton @ 2004-06-25 08:33 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Rosser Schwarz <rosser.schwarz@gmail.com>; pgsql-sql
sad wrote:
> On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
>
>>On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
>>
>>>>Very simply, a boolean may have to values: true or false. It's also
>>>>possible that it's not been set to anything (NULL).
>>>
>>>really ?
>>>what about (13 < NULL)::BOOL
>>
>>Per the semantics of NULL, 13 is neither greater than nor less than
>>NULL. NULL is the *unknown* value; it's impossible to meaningfully
>>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
>>also.
>
>
> READ THE THREAD BEFORE ANSWER
WHAT MAKES YOU THINK HE HASN'T?
OH, AND WHY ARE WE SHOUTING?
Your main argument seems to be:
1. A boolean value may have 3 states: true/false/null
2. The "if" statement has only if/then/else/endif
3. The "if" statement needs to be expanded to include "elsenull" or
similar.
Now, you can argue for/against special-casing nulls, but all other
languages cope with defined(), isset() etc. tests.
The key point of argument, and where the problem is with your (13 <
NULL)::BOOL point is this:
- Boolean _values_ can have one of two states: true/false
- Boolean _variables_ can have one of three states: true/false/null
What you had in your example was an undefined boolean variable, not a
boolean value with a third state. You can argue that the null in
question is of type boolean (I don't), but it is explicitly _not_ a value.
Now, your point that:
IF (...)=true
is not the same as
IF NOT((...)=false)
is a good one. Handling of NULLs causes a great deal of pain to
inexperienced and experienced developers alike. You might be interested
in the archives at http://www.dbdebunk.com/ which IIRC contains some
articles arguing against nulls at all in a relational system.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 09:21 Richard Huxton <dev@archonet.com>
parent: sad <sad@bankir.ru>
0 siblings, 1 reply; 33+ messages in thread
From: Richard Huxton @ 2004-06-25 09:21 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: Rosser Schwarz <rosser.schwarz@gmail.com>; pgsql-sql
sad wrote:
>>sad wrote:
>>
>>>On Friday 25 June 2004 09:37, Rosser Schwarz wrote:
>>>
>>>>On Fri, 25 Jun 2004 08:16:47 +0400, sad <sad@bankir.ru> wrote:
>>>>
>>>>>>Very simply, a boolean may have to values: true or false. It's also
>>>>>>possible that it's not been set to anything (NULL).
>>>>>
>>>>>really ?
>>>>>what about (13 < NULL)::BOOL
>>>>
>>>>Per the semantics of NULL, 13 is neither greater than nor less than
>>>>NULL. NULL is the *unknown* value; it's impossible to meaningfully
>>>>compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
>>>>also.
>>>
>>>READ THE THREAD BEFORE ANSWER
>>
>>WHAT MAKES YOU THINK HE HASN'T?
>
>
> I had answered to the proposal to PROHIBIT NULL VALUES
Umm - what proposal?
Geoffrey wrote:
> Very simply, a boolean may have to values: true or false. It's also
> possible that it's not been set to anything (NULL).
You replied:
> really ?
> what about (13 < NULL)::BOOL
Which is an example where a boolean variable is undefined/not set/null.
In reply to you, Rosser Schwarz wrote:
> Per the semantics of NULL, 13 is neither greater than nor less than
> NULL. NULL is the *unknown* value; it's impossible to meaningfully
> compare it to anything else. Try (NULL = NULL)::boolean. It's NULL,
> also.
>
> Since no value, including NULL, is in any way definitively comparable
> to NULL -- the unknown value -- comparing to NULL results in ...
> unknown.
>
> Otherwise known as NULL.
None of which suggests prohibiting nulls.
>
>>The key point of argument, and where the problem is with your (13 <
>>NULL)::BOOL point is this:
>
>
> IT IS NOT MY PROBLEM !!! it is an EXAMPLE WHY WE CAN NOT PROHIBIT NULLS !!!
Umm - who is suggesting prohibiting nulls? I've re-read the entire
thread and can't find any such suggestion. Is this one of those
occasions where the different dialects of English are causing confusion?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 09:27 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Radu-Adrian Popescu <radu.popescu@aldratech.com>
1 sibling, 0 replies; 33+ messages in thread
From: Radu-Adrian Popescu @ 2004-06-25 09:27 UTC (permalink / raw)
To: sad <sad@bankir.ru>; +Cc: pgsql-sql
sad wrote:
>>You can think of values in plpgsql as wrapper objects that carry a value
>>and have a "is_null" flag; I have no idea how they're implemented in
>>PostgreSQL or in any RDMBS in general but this should do it, at least for a
>>naive implementation.
>
>
>
> Why should i think on simple object MUCH more complicated than it in nature is
> ?
> Let we discuss plpgsql here. Leave the Java to Javers.
>
>
>
I _am_ discussing plpgsql here. And I'm also trying to shed some light on this one.
Now, you think of these things as "MUCH more complicated" because they are NOT
simple objects, and if you think that having an integer value that can be NULL
is complicated, perhaps you should stick to assembly.
Now, I'm no specialist on this, but it is quite obvious that any data type
instance in SQL can have values from:
- its natural domain (arrays of characters, numbers, ip addresses, dates, binary
data and so on)
- NULL
Now think of where else you've seen this. It's quite obvious that
"function foo(int)" in plpgsql is like "void foo(Integer i)" or like "void
foo(int* i)" and NOT like "void foo(int i)". Now the latter does not put any
problems to anyone, does it ? You check that the reference is set and then
proceed to use it's value.
Also you might consider giving it a rest and stop shouting in the forum - it's
not your back yard you know.
Peace,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
^ permalink raw reply [nested|flat] 33+ messages in thread
* Re: feature request ?
@ 2004-06-25 18:28 Jaime Casanova <systemguards@yahoo.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 33+ messages in thread
From: Jaime Casanova @ 2004-06-25 18:28 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I ask: "why not to disallow nulls in boolean fields?". It was a question not a proposal.
The explanation was clear to me. Nulls are not values but the absence of a known value.
It is comparable to the state of a c (or almost any other programming language) variable that had not been initialized. It is not a matter to disallw it, because is implicit in the nature of the programming languages. Am i right?
(Maybe my english is not perfect, is not my mother tongue but a lerned one.)
Thanx in advance,
Jaime Casanova
Richard Huxton <dev@archonet.com> wrote:
Umm - what proposal?
Umm - who is suggesting prohibiting nulls? I've re-read the entire
thread and can't find any such suggestion. Is this one of those
occasions where the different dialects of English are causing confusion?
--
Richard Huxton
Archonet Ltd
---------------------------------
Do You Yahoo!?
Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
Visíta Yahoo! Noticias.
^ permalink raw reply [nested|flat] 33+ messages in thread
end of thread, other threads:[~2004-06-25 18:28 UTC | newest]
Thread overview: 33+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-21 08:48 feature request sad <sad@bankir.ru>
2004-06-23 09:55 feature request ? sad <sad@bankir.ru>
2004-06-23 17:12 ` Re: feature request ? Josh Berkus <josh@agliodbs.com>
2004-06-24 05:32 ` Re: feature request ? Michael Glaesemann <grzm@myrealbox.com>
2004-06-24 04:56 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-24 10:32 ` Re: feature request ? Iain <iain@mst.co.jp>
2004-06-24 10:24 ` Re: feature request ? Michael Glaesemann <grzm@myrealbox.com>
2004-06-24 09:39 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-24 15:51 ` Re: feature request ? Michael Glaesemann <grzm@myrealbox.com>
2004-06-24 17:32 ` Re: feature request ? Jaime Casanova <systemguards@yahoo.com>
2004-06-24 19:27 ` Re: feature request ? Andrew Sullivan <ajs@crankycanuck.ca>
2004-06-24 05:01 Re: feature request ? sad <sad@bankir.ru>
2004-06-24 07:48 Re: feature request ? Gregory S. Williamson <gsw@globexplorer.com>
2004-06-24 07:04 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-24 10:32 ` Re: feature request ? Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-24 09:43 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-24 13:25 ` Re: feature request ? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-24 12:55 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-24 14:00 ` Re: feature request ? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-24 13:24 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-24 14:34 ` Re: feature request ? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-24 17:43 ` Re: feature request ? Bruno Wolff III <bruno@wolff.to>
2004-06-24 18:24 ` Re: feature request ? Radu-Adrian Popescu <radu.popescu@aldratech.com>
2004-06-24 22:43 ` Re: feature request ? Geoffrey <esoteric@3times25.net>
2004-06-25 09:27 ` Re: feature request ? Radu-Adrian Popescu <radu.popescu@aldratech.com>
2004-06-24 18:41 ` Re: feature request ? Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-06-25 04:16 Re: feature request ? sad <sad@bankir.ru>
2004-06-25 05:37 ` Re: feature request ? Rosser Schwarz <rosser.schwarz@gmail.com>
2004-06-25 06:20 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-25 08:33 ` Re: feature request ? Richard Huxton <dev@archonet.com>
2004-06-25 07:54 ` Re: feature request ? sad <sad@bankir.ru>
2004-06-25 09:21 ` Re: feature request ? Richard Huxton <dev@archonet.com>
2004-06-25 18:28 ` Re: feature request ? Jaime Casanova <systemguards@yahoo.com>
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