Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Poor man's Stored Procedures with SQLite
DROP VIEW IF EXISTS stored_procedure_do;
CREATE VIEW IF NOT EXISTS stored_procedure_do
AS SELECT NULL AS json_args LIMIT 1;
DROP TABLE IF EXISTS stored_procedure_result;
CREATE TABLE IF NOT EXISTS stored_procedure_result(
json_result
);
DROP TRIGGER IF EXISTS stored_procedure_impl;
CREATE TRIGGER stored_procedure_impl
INSTEAD OF UPDATE ON stored_procedure_do
BEGIN
DELETE FROM stored_procedure_result;
INSERT INTO stored_procedure_result
SELECT json_array(key,value)
FROM json_each(NEW.json_args);
END;
UPDATE stored_procedure_do SET json_args = '{"a":3,"z":4}';
SELECT * FROM stored_procedure_result;
-- ["a",3]
-- ["z",4]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.