Postgres FTS with array of composite types

You can create a function to get the array parts and returns as a SETOF quotes and call the funcion on the SELECT:

CREATE OR REPLACE FUNCTION getquote(posts)
RETURNS SETOF quotes
LANGUAGE sql
AS $getquote$
SELECT $1.q[i].text, $1.q[i].is_direct
FROM generate_series(array_lower($1.q, 1), array_upper($1.q, 1)) AS i
$getquote$

And the SELECT would be:

SELECT body, (getquote(p)).* FROM posts p;

You could create a view to make things easier, and you can also do filter tasks on the function itself.

Regards,

Matheus de Oliveira

The following code will "filter" your array. select (unnest(q)).text from posts;. unnest pivots the array into rows. Notice that unnest is wrapped in parenthesis, this is important because without the parenthesis you wouldn't be able to select specific fields -- i.e., unnest(q).text is invalid and unnest(q) itself results in a row containing the composite in string form.

The code to treat the post txt and the quote txt's as a single unit of text is as follows :

SELECT to_tsvector(q.b_txt || q.q_t) FROM
(
    SELECT b_txt,string_agg(p.q_txt,' ') as q_t 
    FROM ( SELECT 1 AS id,body b_txt, (unnest(q)).text AS q_txt FROM posts) AS p 
    GROUP BY p.id,b_txt
) AS q;

This probably needs to be adapted into a UDF for use in gin or gist indexes.

I can answer part 1.

To return a row for each body/quote:

SELECT body, q_unnest.text AS quote
FROM posts, UNNEST(q) AS q_unnest

To return a row for each post with an array of quote texts:

SELECT posts.body, array_agg(q_unnest.text) AS quotes
FROM posts, UNNEST(q) AS q_unnest
GROUP BY posts.body

To return a row for each post with quote texts separated by a delimiter:

SELECT posts.body, array_to_string(array_agg(q_unnest.text), '|') AS quotes
FROM posts, UNNEST(q) AS q_unnest
GROUP BY posts.body

I setup an SQLFiddle so you can try it for yourself.

As for part 2 and indexing an array of composite types, I believe it requires an "Operator Class", as per the Postgres extensions guide here. That looks like a lot of bother to me and I haven't found an example of how to do it.