From: | "Matt Arnilo S(dot) Baluyos (Mailing Lists)" <matt(dot)baluyos(dot)lists(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Accessing fields in a function that returns a set of composite type - How? |
Date: | 2006-04-18 17:37:37 |
Message-ID: | d1a6d7930604181037i61d9fcc7oc9b51f2abeacce53@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Arnilo S. Baluyos (Mailing Lists) | 2006-04-18 17:45:27 | Re: Accessing fields in a function that returns a set of composite type - How? |
Previous Message | Robert Landsmeer | 2006-04-18 15:49:08 | Re: Find all foreign keys and dropping them from a script. |