Brandon Metcalf wrote: > Assume I have an UPDATE statement that looks like > > UPDATE foo > SET > pattern = '$pattern', > shape = '$shape', > length = $length, > comment = '$comment' > WHERE foo_id = $foo_id > > and length is defined as NUMERIC. Is there any kind of magic that > would allow me to use the SQL above as is even if $length is not > defined? In other words, I'd like to avoid having to modify the SQL > to include or not include "length = $length" based on whether or not > $length is defined as it's acceptable for it to be NULL in foo. No. There's a reason SQL is a pain in the ass to work with at an application level and people are always coming up with abstraction layers and generators for it. What language are you using? Are you familiar with the problem of SQL injection? You need to be really paranoid about user input. Otherwise someone will do this to you: http://xkcd.com/327/ ... so you've got more to worry about than just handling undefined inputs. Consider using existing tools provided in your database driver or language that handle these issues for you. > I can't say "length = '$length'" as '' is not valid input for NUMERIC. ... and because if it was, it'd probably map to NULL, so you wouldn't be saying "don't change `length'" you'd be saying "set length to NULL". Do you want to leave length unchanged if $length is undefined? or do you want to set length to null if $length is undefined? If you want to set length to null if $length is undefined, use a parameterised query. Any decent database driver will map "undefined" variables in a language to NULL parameters, so you can do something like: $sql = " UPDATE foo SET pattern = ?, shape = ?, length = ?, comment = ? WHERE foo_id = ? "; db.execute($sql, $pattern, $shape, $length, $comment, $foo_id); ( adapt as necessary to your language's syntax, database driver, parameter placeholder syntax, etc). If you wanted to leave `length' unchanged when $length is not defined, instead of setting it to null, you could use the following somewhat ugly trick: $sql = " UPDATE foo SET pattern = ?, shape = ?, length = COALESCE(?, foo.length), comment = ? WHERE foo_id = ? "; db.execute($sql, $pattern, $shape, $length, $comment, $foo_id); -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
