pgswish/trunk/pgswish.sql

CREATE OR REPLACE FUNCTION plperlswish(text, text, text, text, text) RETURNS boolean AS '
        my ($table, $index_path, $query, $properties, $sort) = @_;

        if (! -e $index_path) {
                elog ERROR,"can\'t find swish-e index $index_path";
                return;
        }

        if (! -e $index_path) {
                elog ERROR,"swish-e index $index_path exists, but is not readable";
                return;
        }

        use DBD::PgSPI;
        use SWISH::API;

        $pg_dbh->do("CREATE temporary TABLE $table (
                rank int,
                path text,
                headline text,
                size int,
                properties text[],
                primary key(path)
        )
        ");

        my $swish = SWISH::API->new( $index_path );
        $swish->AbortLastError if $swish->Error;

        my $results = $swish->Query( $query );
        $swish->AbortLastError if $swish->Error;

        my $sth = $pg_dbh->prepare("insert into $table values (?, ?, ?, ?, ?)") || die $sth->errstr;

        while ( my $result = $results->NextResult ) {
                my @data = (
                        $result->Property( "swishrank" ),
                        $result->Property( "swishdocpath" ),
                        $result->Property( "swishtitle" ),
                        $result->Property( "swishdocsize" ),
                );

                my @props;

                foreach my $p (split(/\s+/,$properties)) {
                        push @props, $result->Property( $p );
                }

                push @data, "{\'".join("\',\'", @props)."\'}";

                $sth->execute(@data) || die $sth->errstr;
        }

        return true;
' LANGUAGE plperlu;

drop type __swish_results cascade;
create type __swish_results as (
                rank int,
                path text,
                headline text,
                size int,
                properties text[]
);

create or replace function swish(text,text,text,text) returns setof __swish_results as '
declare
        row record;
begin
        execute ''select plperlswish(''''swish_results'''',
                ''''''||$1||'''''',
                ''''''||$2||'''''',
                ''''''||$3||'''''',
                ''''''||$4||'''''') '' ;

        for row in execute ''select * from swish_results'' loop
                return next row;
        end loop;
        execute ''drop table swish_results'' ;
        return;
end;
' language 'plpgsql';
1	CREATE OR REPLACE FUNCTION plperlswish(text, text, text, text, text) RETURNS boolean AS '
2	my ($table, $index_path, $query, $properties, $sort) = @_;
3
4	if (! -e $index_path) {
5	elog ERROR,"can\'t find swish-e index $index_path";
6	return;
7	}
8
9	if (! -e $index_path) {
10	elog ERROR,"swish-e index $index_path exists, but is not readable";
11	return;
12	}
13
14	use DBD::PgSPI;
15	use SWISH::API;
16
17	$pg_dbh->do("CREATE temporary TABLE $table (
18	rank int,
19	path text,
20	headline text,
21	size int,
22	properties text[],
23	primary key(path)
24	)
25	");
26
27	my $swish = SWISH::API->new( $index_path );
28	$swish->AbortLastError if $swish->Error;
29
30	my $results = $swish->Query( $query );
31	$swish->AbortLastError if $swish->Error;
32
33	my $sth = $pg_dbh->prepare("insert into $table values (?, ?, ?, ?, ?)") \|\| die $sth->errstr;
34
35	while ( my $result = $results->NextResult ) {
36	my @data = (
37	$result->Property( "swishrank" ),
38	$result->Property( "swishdocpath" ),
39	$result->Property( "swishtitle" ),
40	$result->Property( "swishdocsize" ),
41	);
42
43	my @props;
44
45	foreach my $p (split(/\s+/,$properties)) {
46	push @props, $result->Property( $p );
47	}
48
49	push @data, "{\'".join("\',\'", @props)."\'}";
50
51	$sth->execute(@data) \|\| die $sth->errstr;
52	}
53
54	return true;
55	' LANGUAGE plperlu;
56
57	drop type __swish_results cascade;
58	create type __swish_results as (
59	rank int,
60	path text,
61	headline text,
62	size int,
63	properties text[]
64	);
65
66	create or replace function swish(text,text,text,text) returns setof __swish_results as '
67	declare
68	row record;
69	begin
70	execute ''select plperlswish(''''swish_results'''',
71	''''''\|\|$1\|\|'''''',
72	''''''\|\|$2\|\|'''''',
73	''''''\|\|$3\|\|'''''',
74	''''''\|\|$4\|\|'''''') '' ;
75
76	for row in execute ''select * from swish_results'' loop
77	return next row;
78	end loop;
79	execute ''drop table swish_results'' ;
80	return;
81	end;
82	' language 'plpgsql';