Imagine a programming language without functions...This would be tiresome. But that’s what SQL is
SQL limitations encourage creative solutions. I once implemented a simple "functional" framework as a virtual module in SQLite.
Some background. SQLite's virtual tables were always "functional". To make a virtual table function you basically require that particular constraint from where clause is present otherwise you will report a failure. Having this, you may create for example a table of all primes but with two columns, the prime value (obviously) and the count, but the column count will be mandatory (so the query for showing first 10 primes will be 'select value from primes where count=10'). Later SQLite added table-valued syntax and hidden column which allows the use of the syntax 'Select value from primes(10)'. But finally what made me implement a functional framework were eponymous virtual tables. Usually any virtual table should be present in schema, but eponymous tables don't require this and exist just by virtue of the corresponding module being registered.
So the framework uses all this machinery and a simple table is used for storing "functions". The table has two columns, query name and query body. The body may contain numbered SQLite parameters (?1, ?2, ?3). So this single row will automatically create a virtual table with the name from the query name column while all numbered parameters in the body will be converted into "input" parameters of this table (No need to manually parse, SQLite will take care of this thanks to its API). Sure, the bodies can use other queries from this library, SQLite handles this nicely.
SQL limitations encourage creative solutions. I once implemented a simple "functional" framework as a virtual module in SQLite.
Some background. SQLite's virtual tables were always "functional". To make a virtual table function you basically require that particular constraint from where clause is present otherwise you will report a failure. Having this, you may create for example a table of all primes but with two columns, the prime value (obviously) and the count, but the column count will be mandatory (so the query for showing first 10 primes will be 'select value from primes where count=10'). Later SQLite added table-valued syntax and hidden column which allows the use of the syntax 'Select value from primes(10)'. But finally what made me implement a functional framework were eponymous virtual tables. Usually any virtual table should be present in schema, but eponymous tables don't require this and exist just by virtue of the corresponding module being registered.
So the framework uses all this machinery and a simple table is used for storing "functions". The table has two columns, query name and query body. The body may contain numbered SQLite parameters (?1, ?2, ?3). So this single row will automatically create a virtual table with the name from the query name column while all numbered parameters in the body will be converted into "input" parameters of this table (No need to manually parse, SQLite will take care of this thanks to its API). Sure, the bodies can use other queries from this library, SQLite handles this nicely.