From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Brandon Metcalf <brandon(at)geronimoalloys(dot)com> |
Cc: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: quoting values magic |
Date: | 2009-05-26 18:05:33 |
Message-ID: | 87E8174C-B0CE-46D7-9338-055B4429DC9E@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
> j> option 2: case when '$length' = '' ...
>
> j> you can use case like this:
>
> j> UPDATE foo
> j> SET
> j> pattern = '$pattern',
> j> shape = '$shape',
> j> length = case when '$length'='' then length else
> '$length' end,
> j> comment = '$comment'
> j> WHERE foo_id = $foo_id
>
> j> here you can substitute any value you choose for the empty string,
> j> 0 or NULL may (or may not) be more apropriate.
>
>
> The issue here is that these reduce back to my original problem. For
> example, if I use a CASE statement and I fall through to the ELSE,
> then the SQL is attempting to insert a "''" in a NUMERIC field which
> is not valid. That is, it's trying to do
No it doesn't, read that statement again ;)
If $length = 'foo' it reads (leaving out the extra fields):
UPDATE foo
SET length = CASE WHEN 'foo'='' THEN length ELSE 'foo' END
WHERE foo_id = $foo_id;
Which evaluates to:
UPDATE foo SET length = 'foo' WHERE foo_id = $foo_id;
Whereas if $length = '' it reads:
UPDATE foo
SET length = CASE WHEN ''='' THEN length ELSE '' END
WHERE foo_id = $foo_id
Which evaluates to:
UPDATE foo SET length = length WHERE foo_id = $foo_id
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a1c2f7010091048315763!
From | Date | Subject | |
---|---|---|---|
Next Message | Brandon Metcalf | 2009-05-26 18:12:51 | Re: quoting values magic |
Previous Message | Keaton Adams | 2009-05-26 18:03:37 | Need beginning and ending date value for a particular week in the year |