Re: How to insert either a value or the column default?

Would you mind a non-python solution? I think the following will work for
you. It uses an INSERT trigger on the table "tasks". The only minus is
keeping the default value in both the table definition and the trigger. Of
course, it is not really needed in the definition of the row value in the
table due to the INSERT trigger supplying it anyway. For multiple possible
fields, just have another if sequence.

drop table if exists tasks cascade; -- for redefinition
drop function if exists tasks_status(); --for redefinition

create table tasks (
task_id serial primary key,
title text,
status text not null default 'planned'
);

-- name the function whatever you want.
create function tasks_status() returns trigger as $task_status$
begin
-- see if status is NULL and replace if so
if NEW.status is NULL then
NEW.status := 'planned';
end if;
-- if NEW.other is NULL then
-- NEW.Other := <default value>
-- end if;
-- repeat the if to end if some more it required.
return NEW;
end;
$task_status$ LANGUAGE plpgsql;

-- name the trigger whatever you want. I made the same
-- as the procedure just to keep them "linked" in my
-- mind. Make sure you change the procedure name
-- referenced if you change the function name.
create trigger tasks_status before insert or update on tasks
for each row execute procedure tasks_status();

-- just some example insert commands to
-- test the trigger. took me 10 tries to get
-- it typed in correctly. Stupid fingers! [grin]
insert into tasks(title,status) values('one','active');
insert into tasks(title) values('two');
insert into tasks(title,status) values('three',NULL);

On Sat, Aug 23, 2014 at 1:10 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:

> 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(at)tplus1(dot)com
> http://tplus1.com
>
>
--
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown