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	dpavlin	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';