On Fri, Mar 7, 2014 at 7:37 PM, Max Vlasov <max.vla...@gmail.com> wrote:
> On Fri, Mar 7, 2014 at 6:39 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
>> Max Vlasov wrote:
>>>
>>> Nice suggestion. This probably falls into case when a small new part
>>> needed on sqlite side
>>
>> Actually, no change to SQLite itself would be needed. It's possible
>> to create an extension that provides a function that allows to register
>> another function that executes a custom SQL expression:
>>
>> SELECT register_simple_function('rpad', 2, 'SELECT printf(''%-*s'', ?, ?)');
>>
>>
>
> Brilliant :) this is a solution not requiring adding anything to
> sqlite. I'd only prefer not using full-features sql queries, but
> rather expression that is automatically wrapped in SELECT without
> FROM, otherwise they would create dimensions (columns, rows) when our
> function strictly expected one result. And also support for numbered
> parameters. So my example whould be implemented as
>
> SELECT register_function('complexcase', 2, 'Case when ?1 < 10 then ?2
> else ?2 + 10 end');
>
> It's interesting that such approach makes this super-function
> Pragma-like. It changes some internal state (reveals a new function)
> and also optionally returns some result, for example 1 for success.
OK, reading Max's answer, I realize I misunderstood your original post
Clemens. Sorry about that.
But doesn't the above assume you can bind values inside the select
clause? I thought one couldn't...
The new CTE might work-around that, if that's really the case, since
hopefully it should be possible to use bind variables in the first
statement below, while somehow I thought one couldn't in the second
statement.
sqlite> with args(x, y) as (VALUES('foo', 16)) select printf('%-*s',
y, x) from args;
foo
sqlite> select printf('%-*s', y, x) from (select 'foo' as x, 16 as y);
foo
sqlite>
basically register_function('rpad', 'x', 'y', 'printf(''%-*s'', y,
x)') would register a 2-arg function (register_function's argc-2)
named $argv[0], which executes the following statement
with args($argv[1], $argv[2], ... $argv[argc-2]) as (VALUES(?, ?))
select $argv[argc-1) from args;
with $argv[i] textually replaced. On executing the registered rpad
function, one would forward the args to the inner statement prepared
above via sqlite3_bind_value, and execute it.
Of course, during inner query preparation, one can verify the
expression returns a single scalar value, and at execution time, that
it returns a single row (it can't I think, but I'm not experienced in
SQL injection techniques, so I'd assert it myself ;) I think I might
try to code such a super-function inside our app.
I agree with Max. Brilliant! --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
