Thread: Accessing fields in a function that returns a set of composite type - How?
Accessing fields in a function that returns a set of composite type - How?
From
"Matt Arnilo S. Baluyos (Mailing Lists)"
Date:
Hello everyone, I am using tsearch2 to create a search engine for our website. I am using the tsearch2 guide as my reference and am adapting it to fit our requirements: http://www.rhodesmill.org/brandon/projects/tsearch2-guide.html I have a composite type for search_result. This is the definition: Composite type "public.search_result" Column | Type ---------------+--------- article_id | integer article_title | text headline | text rank | real I then have a pl/pgsql function that returns a set of "search_result" CREATE FUNCTION search(text) RETURNS SETOF search_result LANGUAGE sql $$ SELECT article_id, article_title, headline(article_text, q), rank(article_vector, q) FROM articles, to_tsquery($1) AS q WHERE article_vector @@ q ORDER BY rank(article_vector, q) DESC; To do a full-text search, I only need to do this: SELECT search('string'); However, running the function gives me a recordset with only one column ("search"). I'd like to be able to access the fields of the composite type (search_result) that is returned by the function - article_id, article_title, headline, and rank. I haven't figured out how to do this but it's necessary because I want to be able to control the formatting of the output. Thanks and best regards, Matt -- Stand before it and there is no beginning. Follow it and there is no end. Stay with the ancient Tao, Move with the present.
Re: Accessing fields in a function that returns a set of composite type - How?
From
"Matt Arnilo S. Baluyos (Mailing Lists)"
Date:
On 4/19/06, Matt Arnilo S. Baluyos (Mailing Lists) <matt.baluyos.lists@gmail.com> wrote: > I have a composite type for search_result. This is the definition: > > Composite type "public.search_result" > Column | Type > ---------------+--------- > article_id | integer > article_title | text > headline | text > rank | real > > > I then have a pl/pgsql function that returns a set of "search_result" > > CREATE FUNCTION search(text) RETURNS SETOF search_result LANGUAGE sql $$ > SELECT article_id, article_title, headline(article_text, q), > rank(article_vector, q) > FROM articles, to_tsquery($1) AS q > WHERE article_vector @@ q ORDER BY rank(article_vector, q) DESC; > > To do a full-text search, I only need to do this: > > SELECT search('string'); > > However, running the function gives me a recordset with only one > column ("search"). I'd like to be able to access the fields of the > composite type (search_result) that is returned by the function - > article_id, article_title, headline, and rank. I haven't figured out > how to do this but it's necessary because I want to be able to control > the formatting of the output. Hmmm... figured it out already. Please disregard my previous post. You only need to do a... SELECT * FROM search('search_string'); -- Stand before it and there is no beginning. Follow it and there is no end. Stay with the ancient Tao, Move with the present.
Re: Accessing fields in a function that returns a set of composite type - How?
From
Alan Hodgson
Date:
On April 18, 2006 10:37 am, "Matt Arnilo S. Baluyos (Mailing Lists)" <matt.baluyos.lists@gmail.com> wrote: > To do a full-text search, I only need to do this: > > SELECT search('string'); > > However, running the function gives me a recordset with only one > column ("search"). I'd like to be able to access the fields of the > composite type (search_result) that is returned by the function - > article_id, article_title, headline, and rank. I haven't figured out > how to do this but it's necessary because I want to be able to control > the formatting of the output. > In general, a set-returning function looks like a table, ie: SELECT article_id, article_title, headline, and rank FROM search('string'); Assuming your function works right. I get an error when trying to call a set-returning function as SELECT function();, though, so I don't know how yours is doing anything. -- Alan