SQLite Forum

Request: stored functions/procedures
Login
I don't think functions need to contain entire blocks of SQL codes.

For a scalar function, possibly can use a syntax like:
```
CREATE FUNCTION name(argument_list) AS expr;
```
For a aggregate function (including window functions), possibly can use a syntax like:
```
CREATE AGGREGATE FUNCTION name(argument_list) AS (select_stmt);
```
If you write `AGGREGATE` where a table name is expected in the definition of a aggregate function, then it refers to the data that has been accumulated by the aggregate function. There should also be some way of accessing the row number; this is mainly relevant for window functions, although if it is fixed to allow `ORDER BY` for non-window aggregate functions too (which I think it should allow), then it will be usable with that too.

For procedures, I think that `INSTEAD OF` triggers on views are mostly good enough, although it might help to add:

* Allow `WITH` clauses at the beginning of statements in a trigger program.

* Allow `INSERT INTO` ... `DEFAULT VALUES` inside of a trigger program.

* Allow qualified table names in temporary triggers.

* Add a `FOR` block. The syntax is the same as a `WITH` clause, but with the word `FOR` instead of `WITH`, and it would be followed by `BEGIN`, and then followed by any number of statements and then `END FOR;` at the end.

* Add a `TRY` block, to catch constraint errors resulting from SQL codes inside of the block (whether due to actual table constraints or due to a function or virtual table returning `SQLITE_CONSTRAINT`).

For the `CREATE VIEW` command, allow the list of column names to include data types (which may include `HIDDEN` to be like hidden columns in virtual tables) and default values. (There is already a way to use virtual tables together with joins to extract constraints and use the constrained values in an expression in a view; look for the message elsewhere in the forum and/or mailing list.)