access to lexems or access to parsed elements

From: "Massa, Harald Armin" <harald(at)2ndQuadrant(dot)de>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: access to lexems or access to parsed elements
Date: 2011-08-25 16:21:21
Message-ID: CAMSP2L7q_YXpwHE9Aq=chQ7hchkT41fGH1M2edbturO+L-_HjQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I want to access the single words in a text. Better yet: the relevant words
(i.e. without stop words) in a text.

to_tsvector or casting gets me the lexems as a tsvector:

select to_tsvector('the quick brown fox jumped over the lazy fox')
''brown':3 'fox':4,9 'jump':5 'lazi':8 'quick':2'

And I would like to access "brown", "fox", "jump", "lazi" and "quick" as
single values that I insert into another table.

But: no luck with any tries to convert to records, arrays or similiar.

Next step, the lesser-known-fts-functions:

select ts_parse('default','the quick brown fox jumped over the lazy fox')

(1,the)
(12," ")
(1,quick)
[...]
(1,fox)

is a set-returning-function, giving me 17 records of type pseudo-record.
Stopwords still in there, so what. But: No chance of accessing the second
field in that record.

Of course, there is allways:

select substr(what::text,position(',' in
what::text)+1,char_length(what::text)-position(',' in what::text)-1) from
(
select ts_parse('default','the quick brown fox jumped over the lazy fox') as
what
)x

but, comeon: having a two-field-record, casting it to one field of text,
searching for the "," that separates the two fields and then split the
one-field into two fields by substring?

So, is there a better way to access

a) the lexems of a tsvector
b) the (unnamed) fields of a set-of-record-returning function

?
Harald

--
Harald Armin Massa www.2ndQuadrant.d <http://www.2ndquadrant.com/>e
PostgreSQL Training, Services and Support

2ndQuadrant Deutschland GmbH
GF: Harald Armin Massa
Amtsgericht Stuttgart, HRB 736399

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sushant Sinha 2011-08-25 16:41:16 Re: access to lexems or access to parsed elements
Previous Message uanacho 2011-08-25 15:35:33 rollback doubt and connection to remoteDB