pgestraier/trunk/test.sql

--select * from pgest(null, 'a OR b OR c', '', 0, 0);
--select * from pgest('##dir##/data/casket', 'a OR b OR c', '', 0, 10);
--select * from pgest('##dir##/data/casket', 'a OR b OR c', '', 20, 0);
--select * from pgest('##dir##/data/casket', 'a OR b OR c', null, 20, 0);
--select * from pgest('##dir##/data/casket', 'a OR b OR c', '@uri ISTREW .txt', 100, 0);

-- find all references to blade runner in other films
select uri,title,year from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', '@title !ISTRINC blade runner', null, null, null, array['@uri','@title','year']) as (uri text, title text, year int);

-- find all references and count them to blade runner after 1990
select count(title),title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990', null, null, null, array['@title']) as (title text) group by title order by title;

-- example of new API which allows user to specify attributes
select count(title),year from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', null, null, null, null, array['@title','year']) as (title text, year text) group by year order by year asc;

-- test sort order
select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990', '@title STRA', 5, 0, array['@title']) as (title text);

-- test attributes delimited by {{!}}
select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990{{!}}@title ISTRINC blade', null, null, null, array['@title']) as (title text);

-- node API examples

select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 1, 'blade runner', 'year NUMGT 1990', '@title STRA', 10, 5, array['@title']) as (title text);

-- comparison of direct access and node API
-- explain analyze select title from pgest('##dir##/data/casket', 'blade runner', 'year NUMGT 1990', '@title STRA', 5, 0, array['@title']) as (title text);
explain analyze select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990', '@title STRA', 5, 0, array['@title']) as (title text);

-- example of using convert and hints
select uri,convert(title,'UTF-8', 'LATIN2') as title,year,hits,wordnum,time
from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0,
        'blade', '@title ISTRINC runner', null, null, null,
        array['@uri','@title','year','HINTS.HIT','HINTS.WORDNUM','HINTS.TIME']) as
        (uri text, title text, year int, hits int, wordnum text, time text);

1	--select * from pgest(null, 'a OR b OR c', '', 0, 0);
2	--select * from pgest('##dir##/data/casket', 'a OR b OR c', '', 0, 10);
3	--select * from pgest('##dir##/data/casket', 'a OR b OR c', '', 20, 0);
4	--select * from pgest('##dir##/data/casket', 'a OR b OR c', null, 20, 0);
5	--select * from pgest('##dir##/data/casket', 'a OR b OR c', '@uri ISTREW .txt', 100, 0);
6
7	-- find all references to blade runner in other films
8	select uri,title,year from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', '@title !ISTRINC blade runner', null, null, null, array['@uri','@title','year']) as (uri text, title text, year int);
9
10	-- find all references and count them to blade runner after 1990
11	select count(title),title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990', null, null, null, array['@title']) as (title text) group by title order by title;
12
13	-- example of new API which allows user to specify attributes
14	select count(title),year from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', null, null, null, null, array['@title','year']) as (title text, year text) group by year order by year asc;
15
16	-- test sort order
17	select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990', '@title STRA', 5, 0, array['@title']) as (title text);
18
19	-- test attributes delimited by {{!}}
20	select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990{{!}}@title ISTRINC blade', null, null, null, array['@title']) as (title text);
21
22	-- node API examples
23
24	select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 1, 'blade runner', 'year NUMGT 1990', '@title STRA', 10, 5, array['@title']) as (title text);
25
26	-- comparison of direct access and node API
27	-- explain analyze select title from pgest('##dir##/data/casket', 'blade runner', 'year NUMGT 1990', '@title STRA', 5, 0, array['@title']) as (title text);
28	explain analyze select title from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0, 'blade runner', 'year NUMGT 1990', '@title STRA', 5, 0, array['@title']) as (title text);
29
30	-- example of using convert and hints
31	select uri,convert(title,'UTF-8', 'LATIN2') as title,year,hits,wordnum,time
32	from pgest('http://localhost:1978/node/trivia', 'admin', 'admin', 0,
33	'blade', '@title ISTRINC runner', null, null, null,
34	array['@uri','@title','year','HINTS.HIT','HINTS.WORDNUM','HINTS.TIME']) as
35	(uri text, title text, year int, hits int, wordnum text, time text);
36