SQLite Forum

Getting 'default' values: for a view?
Login

Getting 'default' values: for a view?

(1) By midijohnny on 2021-11-03 22:33:00 [link]

Is there a way of evaluating the 'default' expression for a table in a context other than INSERTing a new row?


### Example:

```
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]

One way is:
```sql
begin; insert into people default values returning *; rollback;
```

(3) By midijohnny on 2021-11-04 17:47:43 in reply to 2 [link]

Nice: I didn't know about the [returning](https://www.sqlite.org/lang_returning.html) 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

Very clever.  I'm impressed.