Re: [sqlite] How to emulate generate_series function?
В сообщении от Wednesday 23 July 2008 15:42:04 Igor Tandetnik написал(а):
> "Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>
> > select * from direction_telephony
> > where prefix in
> > ('78312604812','7831260481','783126048','78312604','7831260','783126','78
> >312','7831','783','78','7') order by length(prefix) desc
> > limit 1;
>
> select * from direction_telephony
> where prefix != '' and '78312604812' LIKE prefix || '%'
> order by length(prefix) desc
> limit 1;
>
> Or
>
> where prefix=substr('78312604812', 1, length(prefix))
>
> Igor Tandetnik
And how about indicies?
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix in
('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony WITH INDEX direction_telephony_prefix
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix in
(substr('78312604812',1,1),substr('78312604812',1,2),substr('78312604812',1,3),
...>
substr('78312604812',1,4),substr('78312604812',1,5),substr('78312604812',1,6),substr('78312604812',1,7),
...>
substr('78312604812',1,8),substr('78312604812',1,9),substr('78312604812',1,10),substr('78312604812',1,11),
...>
substr('78312604812',1,12),substr('78312604812',1,13),substr('78312604812',1,14),substr('78312604812',1,15))
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony WITH INDEX direction_telephony_prefix
sqlite>
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix != '' and '78312604812' LIKE prefix || '%'
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony
sqlite>
sqlite> explain query plan
...> select * from direction_telephony
...> where prefix=substr('78312604812', 1, length(prefix))
...> order by length(prefix) desc
...> limit 1;
0|0|TABLE direction_telephony
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to emulate generate_series function?
I think you are asking about 'table functions', which are functions
that return a rowset and are used in place of a table to generate rows.
See: http://www.postgresql.org/docs/7.3/static/xfunc-tablefunctions.html
To my knowledge this is not supported in sqlite, except perhaps via
virtual table, tho it is not clear to me how you would pass arguments
via this api.
I would love to have a nice simple interface for table functions in
sqlite. If it already exists, I also would like a pointer to the
documentation/examples.
On Jul 23, 2008, at 4:38 AM, Alexey Pechnikov wrote:
> Hello!
>
> How can I emulate PostreSQL function select generate_series?
>
> ==
> Example:
> select generate_series(1,7);
> 1
> 2
> 3
> 4
> 5
> 6
> 7
>
> ==
> My task is this:
>
> create table direction_telephony (
> group_name text not null,
> name text not null,
> class text not null,
> prefix text not null,
> price real not null,
> currency text not null default 'RUB'
> );
>
> insert into direction_telephony values ('Globus
> daily', 'Russia','','7','3.0','RUB');
> insert into direction_telephony values ('Globus daily', 'N.Novgorod
> Region','','7831','2.0','RUB');
> insert into direction_telephony values ('Globus
> daily', 'N.Novgorod','','78312','1.0','RUB');
>
> select * from direction_telephony
> where prefix in
> ('78312604812','7831260481','783126048','78312604','7831260','783126','
> 78312','7831','783','78','7')
> order by length(prefix) desc
> limit 1;
>
> Globus daily|N.Novgorod||78312|1.0|RUB
>
> With generate_series function I can generate
> condition
> "('78312604812','7831260481','783126048','78312604','7831260','783126',
> '78312','7831','783','78','7')"
> inside query.
>
> select substr('78312604812',1,x) from
> generate_series(1,length('78312604812'))
> as x;
>
> "7"
> "78"
> "783"
> "7831"
> "78312"
> "783126"
> "7831260"
> "78312604"
> "783126048"
> "7831260481"
> "78312604812"
>
>
> Best regards, Alexey.
> ___
> 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] How to emulate generate_series function?
"Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> select * from direction_telephony
> where prefix in
> ('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
> order by length(prefix) desc
> limit 1;
select * from direction_telephony
where prefix != '' and '78312604812' LIKE prefix || '%'
order by length(prefix) desc
limit 1;
Or
where prefix=substr('78312604812', 1, length(prefix))
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to emulate generate_series function?
Hello!
How can I emulate PostreSQL function select generate_series?
==
Example:
select generate_series(1,7);
1
2
3
4
5
6
7
==
My task is this:
create table direction_telephony (
group_name text not null,
name text not null,
class text not null,
prefix text not null,
price real not null,
currency text not null default 'RUB'
);
insert into direction_telephony values ('Globus
daily', 'Russia','','7','3.0','RUB');
insert into direction_telephony values ('Globus daily', 'N.Novgorod
Region','','7831','2.0','RUB');
insert into direction_telephony values ('Globus
daily', 'N.Novgorod','','78312','1.0','RUB');
select * from direction_telephony
where prefix in
('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')
order by length(prefix) desc
limit 1;
Globus daily|N.Novgorod||78312|1.0|RUB
With generate_series function I can generate
condition
"('78312604812','7831260481','783126048','78312604','7831260','783126','78312','7831','783','78','7')"
inside query.
select substr('78312604812',1,x) from generate_series(1,length('78312604812'))
as x;
"7"
"78"
"783"
"7831"
"78312"
"783126"
"7831260"
"78312604"
"783126048"
"7831260481"
"78312604812"
Best regards, Alexey.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
