Re: How to insert either a value or the column default? - Mailing list pgsql-general
| From | John McKown |
|---|---|
| Subject | Re: How to insert either a value or the column default? |
| Date | |
| Msg-id | CAAJSdjiMe31ZXTqXKhZ3kU8V=nbtE08tDwHqU5qYtLdaRnWTKA@mail.gmail.com Whole thread Raw |
| In response to | How to insert either a value or the column default? ("W. Matthew Wilson" <matt@tplus1.com>) |
| List | pgsql-general |
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@tplus1.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@tplus1.com
http://tplus1.com
There is nothing more pleasant than traveling and meeting new people!
Genghis KhanMaranatha! <><
John McKown
John McKown
pgsql-general by date: