Getting 'default' values: for a view?
(1) By midijohnny on 2021-11-03 22:33:00 [link] [source]
Is there a way of evaluating the 'default' expression for a table in a context other than INSERTing a new row?
create table people(name text default 'fred'); insert into people default values; -- will insert 'fred' insert into people values(null);
I am wondering if there is some construction that would allow me to generate the row - without actually necessarily persisting it - in the same way?
select ifnull(name,'I would like the default here') from people;
I can see how to see the expression; so for static strings , you can do this:
SELECT TRIM(dflt_value,"'") -- bad assumption FROM pragma_table_info('people') WHERE NAME='name'
The TRIM here is to remove the single quotes from the default; but that only works of course if the default happens to be a static string, rather than a calculated one.
(2) By anonymous on 2021-11-03 23:33:53 in reply to 1 [link] [source]
One way is:
begin; insert into people default values returning *; rollback;
(3) By midijohnny on 2021-11-04 17:47:43 in reply to 2 [source]
Nice: I didn't know about the returning clause - could be useful for other things as well.
It won't quite work for what I need at the moment (but I see there are some features being looked at for future releases) - but good to know of this. Thanks!
(4) By Simon Slavin (slavin) on 2021-11-04 22:15:40 in reply to 2 [link] [source]
Very clever. I'm impressed.