I have a table that looks sort of like this:
create table tasks
(
task_id serial primary key,
title text,
status text not null default 'planned'
);
In python, I have a function like this:
def insert_task(title, status=None):
....
and when status is passed in, I want to run a SQL insert statement like this:
insert into tasks
(title, status)
values
(%s, %s)
but when status is not passed in, I want to run this SQL insert instead:
insert into tasks
(title, status)
values
(%s, default)
I know how to pick the query with an if-clause in python, but I wish
it were possible to do something like this:
insert into tasks
(title, status)
values
(%s, coalesce(%s, default))
I have tried different variations, but I keep getting syntax errors.
Is there any way to do say:
"if the value is not null, insert the value. Otherwise, insert
the default value for this column"
entirely in SQL?
When there is just one optional column, it is not a big deal to use an
if-clause in python. But there are numerous optional columns.
I know I could build up lists of strings in python but I'm hoping
there's a simpler way to do this in the query.
But I have a hard time already getting other programmers to understand
SQL injection attacks and if they see me building up SQL queries from
strings, even though there's no risk of a SQL injection in this
scenario, I still don't want to break my "no string interpolation"
rule of thumb unless I absolutely have to.
And I know I could switch to some gigantic library like SQLAlchemy,
but I really don't want to.
Any advice is welcome. Thanks in advance!
Matt
--
W. Matthew Wilson
matt@tplus1.com
http://tplus1.com