Re: tsearch2 and unexpected exists (Function defn)

On Sat, 6 Sep 2003, Nigel J. Andrews wrote:

> On Sat, 6 Sep 2003, Nigel J. Andrews wrote:
>
> > On Thu, 4 Sep 2003, Teodor Sigaev wrote:
> >
> > >
> > >
> > > Nigel J. Andrews wrote:
> > > >
> > > > This will be a little vague, it was last night and I can't now do the test in
> > > > that db (see below) so can't give the exact wording.
> > > >
> > > > I seem to remember a report a little while ago about tsearch v2 causing
> > > > unexpected backend exit messages with 7.3.4 and now I'm getting similar
> > > > messages unpredictably and I can't find the thread in the archives either.
> > > > ...
> > >
> > > Have you a core file, if yes then send gdb output, pls...
> >
> >
> >
> > Unfortunately it was only after getting this core file that I noticed I don't
> > have it built with debugging symbols. However, as a starting point, here's the
> > psql session that kicks the core dump, the entire log for the server (although
> > ...
>
> Oh, one snippet I forgot to mention. I changed the permissions for the
> installed tsearch bits by allowing public usage on the schema and select on the
> 4 tsearch2 tables contained in it. Could this be significant?

And it just occurred to me I didn't include the function definition before
which would be quite good considering it's perhaps the interaction between
plgpsql and tsearch2.

--
Nigel J. Andrews


Function and related definitions:

CREATE TYPE article_search AS    (
    article_content_id    integer
    ,article_id                integer
    ,name                        text
    ,summary                    text
);



CREATE OR REPLACE FUNCTION basic_search ( text )
    RETURNS setof article_search
    AS '
    DECLARE
        myrec      article_search%ROWTYPE;
    BEGIN
        FOR myrec IN
            SELECT
                    ac.id
                    ,ac.article_id
                    ,ac.name
                    ,ac.summary
                FROM
                    article_content ac
                    ,article_status s
                WHERE
                        ac.status_id = s.id
                    AND
                        s.name = ''Live''
                    AND
                        ac.search1 @@ to_tsquery(''default'', $1)
        LOOP

            RETURN NEXT myrec;
        END LOOP;

        RETURN;
    END;
    '
    LANGUAGE 'plpgsql';