Re: [sqlite] Custom functions, variable parameters, and SELECT triggers
Thank you both for the thoughtful responses. Comments:
Re: NEW.* -- Thanks Igor for pointing out that my assumptions were totally
off base. Assumptions are tricky things!
Re: "Why can't the trigger call myFunc(new.colA, new.colB)" -- that would
definitely work, but I'd like to make the trigger independent from the
table schema, such that I could add this trigger to a table without even
knowing its schema. The ultimate goal is to just automatically add it to
every table in the database, and thereby monitor all changes to all rows on
all tables -- without needing to hand-roll a bunch of triggers for each.
PRAGMA table_info() - Hm, that's a very clever technique. My original goal
was to create a reusable trigger query that can be applied verbatim to any
table, that will pass all columns to a function for each row change. It's
sounding like this isn't possible, unfortunately. But with your trick, I
could at least programmatically generate the appropriate trigger query as
follow:
1) Get a list of all tables
2) For each table, get a list of all columns with PRAGMA table_info()
3) Assemble and execute the appropriate query for each table to create a
comprehensive trigger.
Cool, thanks for the helpful tips!
-david
On Fri, Jan 30, 2015 at 9:31 PM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:
> On Sat, 2015-01-31 at 00:04 -0500, Igor Tandetnik wrote:
> > On 1/30/2015 10:44 PM, David Barrett wrote:
> > > Is it possible to create a trigger that calls a custom function and
> passes
> > > in NEW.*?
> >
> > Not literally NEW.* . You'll have to spell out individual columns as
> > parameters.
> >
> > > 2) I'm *assuming* if you pass a "*" into that function, it'll just call
> > > that function with however many columns are available.
> >
> > Your assumption is incorrect. If I recall correctly, the syntax
> > "myFunc(*)" is accepted, and is equivalent to "myFunc()" - that is, no
> > parameters are passed. Pretty much the only reason to allow this syntax
> > is to accept "count(*)".
> >
> > > 3) It seems that there is a way to create a custom trigger that has no
> > > byproduct but to call SELECT. The only reason I can imagine you'd
> want to
> > > do that is to call a custom function. But can I call that function
> with
> > > all columns using a *? (I can't quite figure this out from the docs
> alone.)
> >
> > Well, you could have tested it, and discovered it didn't work. You don't
> > even need a custom function, you could have used a built-in one.
> >
> > > SELECT myFunc( NEW.* );
> >
> > That would produce a syntax error.
> >
> > > Are these assumptions correct, and should the above generally work?
> >
> > No, and no.
> >
> > > My
> > > goal is to execute myFunc() every time there's an INSERT/UPDATE/DELETE
> on a
> > > given table -- but I want myFunc() to be reusable and not need to know
> the
> > > structure of the table it's being called on.
> >
> > I'm not sure how the necessity of myFunc(NEW.*) syntax follows from
> > this. Why can't the trigger call myFunc(new.colA, new.colB)?
> >
> > You can write a variadic custom function (one that can be called with an
> > arbitrary number of arguments), if that's what you are asking.
>
> Additional note,
>
> In order to generate queries on tables for which you dont know their
> structure (I've found this particularly useful in dynamic schema
> upgrades), you might find this useful:
>
> PRAGMA table_info ('table_name')
>
> This part should help you to generate a query which passes all the
> columns of a given table to myFunc()
>
> Cheers,
> -Tristan
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom functions, variable parameters, and SELECT triggers
On Sat, 2015-01-31 at 00:04 -0500, Igor Tandetnik wrote:
> On 1/30/2015 10:44 PM, David Barrett wrote:
> > Is it possible to create a trigger that calls a custom function and passes
> > in NEW.*?
>
> Not literally NEW.* . You'll have to spell out individual columns as
> parameters.
>
> > 2) I'm *assuming* if you pass a "*" into that function, it'll just call
> > that function with however many columns are available.
>
> Your assumption is incorrect. If I recall correctly, the syntax
> "myFunc(*)" is accepted, and is equivalent to "myFunc()" - that is, no
> parameters are passed. Pretty much the only reason to allow this syntax
> is to accept "count(*)".
>
> > 3) It seems that there is a way to create a custom trigger that has no
> > byproduct but to call SELECT. The only reason I can imagine you'd want to
> > do that is to call a custom function. But can I call that function with
> > all columns using a *? (I can't quite figure this out from the docs alone.)
>
> Well, you could have tested it, and discovered it didn't work. You don't
> even need a custom function, you could have used a built-in one.
>
> > SELECT myFunc( NEW.* );
>
> That would produce a syntax error.
>
> > Are these assumptions correct, and should the above generally work?
>
> No, and no.
>
> > My
> > goal is to execute myFunc() every time there's an INSERT/UPDATE/DELETE on a
> > given table -- but I want myFunc() to be reusable and not need to know the
> > structure of the table it's being called on.
>
> I'm not sure how the necessity of myFunc(NEW.*) syntax follows from
> this. Why can't the trigger call myFunc(new.colA, new.colB)?
>
> You can write a variadic custom function (one that can be called with an
> arbitrary number of arguments), if that's what you are asking.
Additional note,
In order to generate queries on tables for which you dont know their
structure (I've found this particularly useful in dynamic schema
upgrades), you might find this useful:
PRAGMA table_info ('table_name')
This part should help you to generate a query which passes all the
columns of a given table to myFunc()
Cheers,
-Tristan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom functions, variable parameters, and SELECT triggers
On 1/30/2015 10:44 PM, David Barrett wrote: Is it possible to create a trigger that calls a custom function and passes in NEW.*? Not literally NEW.* . You'll have to spell out individual columns as parameters. 2) I'm *assuming* if you pass a "*" into that function, it'll just call that function with however many columns are available. Your assumption is incorrect. If I recall correctly, the syntax "myFunc(*)" is accepted, and is equivalent to "myFunc()" - that is, no parameters are passed. Pretty much the only reason to allow this syntax is to accept "count(*)". 3) It seems that there is a way to create a custom trigger that has no byproduct but to call SELECT. The only reason I can imagine you'd want to do that is to call a custom function. But can I call that function with all columns using a *? (I can't quite figure this out from the docs alone.) Well, you could have tested it, and discovered it didn't work. You don't even need a custom function, you could have used a built-in one. SELECT myFunc( NEW.* ); That would produce a syntax error. Are these assumptions correct, and should the above generally work? No, and no. My goal is to execute myFunc() every time there's an INSERT/UPDATE/DELETE on a given table -- but I want myFunc() to be reusable and not need to know the structure of the table it's being called on. I'm not sure how the necessity of myFunc(NEW.*) syntax follows from this. Why can't the trigger call myFunc(new.colA, new.colB)? You can write a variadic custom function (one that can be called with an arbitrary number of arguments), if that's what you are asking. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Custom functions, variable parameters, and SELECT triggers
Is it possible to create a trigger that calls a custom function and passes in NEW.*? To break that question down: 1) I know it's possible to create custom functions that take a variable number of parameters. 2) I'm *assuming* if you pass a "*" into that function, it'll just call that function with however many columns are available. For example, this would call myFunc() with two parameters: CREATE TABLE foo ( colA INTEGER, colB TEXT ); SELECT myFunc( * ) FROM foo; 3) It seems that there is a way to create a custom trigger that has no byproduct but to call SELECT. The only reason I can imagine you'd want to do that is to call a custom function. But can I call that function with all columns using a *? (I can't quite figure this out from the docs alone.) CREATE TRIGGER myFuncOnFooInsert AFTER INSERT ON foo BEGIN SELECT myFunc( NEW.* ); END Are these assumptions correct, and should the above generally work? My goal is to execute myFunc() every time there's an INSERT/UPDATE/DELETE on a given table -- but I want myFunc() to be reusable and not need to know the structure of the table it's being called on. Thanks, as always, I appreciate your help! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
