j == jasen@xxxxxxxxxx writes: j> On 2009-05-22, Brandon Metcalf <brandon@xxxxxxxxxxxxxxxxxx> wrote: j> > Assume I have an UPDATE statement that looks like j> > j> > UPDATE foo j> > SET j> > pattern = '$pattern', j> > shape = '$shape', j> > length = $length, j> > comment = '$comment' j> > WHERE foo_id = $foo_id j> > j> > and length is defined as NUMERIC. Is there any kind of magic that j> > would allow me to use the SQL above as is even if $length is not j> > defined? j> no, but you can get the same effect in a different way. j> > In other words, I'd like to avoid having to modify the SQL j> > to include or not include "length = $length" based on whether or not j> > $length is defined as it's acceptable for it to be NULL in foo. j> > I can't say "length = '$length'" as '' is not valid input for NUMERIC. j> > j> > Hope that makes sense? j> option 1: $length = "length" j> If your language of choice (it appears to be similar to shell, PHP, or Perl) j> allows you to store the string value "length" in your $length variable j> then the existing values of length will be retained in the update. 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 UPDATE foo SET length='' WHERE foo_id=$foo_id This fails. It's exactly this problem I'm trying to avoid by some slick use of SQL. I can always generate the SQL on the fly based on the value of $length, but I'd like to avoid this if possible. Maybe I'm misunderstanding your suggestion. -- Brandon -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
