On Tue, Dec 23, 2014 at 05:26:00PM +0200, Tony Papadimitriou wrote:
> Proposal:
>
> Minimal stored procedure functionality -- or, maybe call it stored
It's already there. It's called TRIGGER.
I use triggers to hold "stored procedures", and WHERE conditions on DMLs
in the trigger body as IF..THEN..ELSEs.
CREATE VIEW foo AS
SELECT null AS arg0, null AS arg1, .., null as argN LIMIT 0;
CREATE TRIGGER foo_ins INSTEAD OF INSERT ON foo
FOR EACH ROW
BEGIN
-- INSERT/UPDATE/DELETE/SELECT RAISE(...) statements here
INSERT INTO .. SELECT .. WHERE <condition0>;
INSERT INTO .. SELECT .. WHERE <condition1>;
..
INSERT INTO .. SELECT .. WHERE <conditionN>;
...
END;
Invoking a stored procedure requires an INSERT INTO <view-name> .., with
the columns of the inserted row(s) being the arguments. You can't
SELECT from a stored procedure, but it can store results in a TEMP TABLE
you can SELECT from.
Need looping? Use recursive queries and/or recursive triggers.
Need local variables? Use TEMP TABLEs (which you have to clean up), or
pass them as arguments to yet more stored procedures (recursive
triggers).
There's no need for a trigger rollback journal (I think!) for an insert
into a VIEW with an INSTEAD OF trigger, so it's not too bad.
There's no syntactic sugar for this. It could be added; not much
machinery would be needed beyond the sugar, just some naming conventions
for the views and triggers.
I also use virtual tables as table-valued user-defined functions. A bit
of syntactic sugar for that too would be nice.
> CREATE PROC sample AS (
> SELECT table1.* FROM table1,...,tableN
> WHERE ... possibly complicated join ...
> AND last_name like :1
> ORDER BY :2;
> );
>
> @sample 'Smith%',tax_id
>
> would become:
>
> SELECT table1.* FROM table1,...,tableN
> WHERE ... possibly complicated join ...
> AND last_name like 'Smith%'
> ORDER BY last_name,tax_id;
Yes, this would be nice: UDFs written in SQL.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
