Reading through PostgreSQL ranked results

Question

I'm new to PostgreSQL and I am working on a function to return the word locations for a searched word.

I want to first narrow down the text fields the search has to go though to make sure it is a relevant result from the database.

My table name is 'testing' then the text field column is called 'context' and the line number where it is located is called 'line_number'. Where the context text is associated with a specific line_number.

Right now my ranking code looks like this:

select line_number into lineLocation
from (
    SELECT
        testing.line_number,
        ts_rank_cd(to_tsvector('english', testing.context), 
        to_tsquery('Cats & Dogs & Kids')) AS score

    FROM Testing
) ranking
WHERE score >0 
ORDER BY score DESC;
Return QUERY select * from lineLocation;

When I try to print out lineLocation as a return query, it works in reporting the new ranked line numbers 22,19,21,20,17,13 each returned in their own column.

My problem now is that I want to search each of those lines (22 ... 13) for a key word like "dog" and return its position

Obtaining the text for that by using:

select context into sample from testing
  where testing.line_number = lineLocation;

If I try to just decrement the lineLocation in a loop like lineLocation -i It goes out of order, and will eventually search context that is not relevant.

Is there any type of 'read next line' function I could use? I am looking for a way to loop through the ranked result line numbers

EDIT I then go on to use a for loop where I want it to read through all of the rows of text in the column context from the ranked results

The problem I am having with this is that it only reads the first row of text in the column 'context' and I need it to look at all of the rows that are returned by the ranked search

Answer 1

Ended up creating a ranking function of its own, and inserting the results of that text search into another table with a serial increment column.

filled the values of the new table (ranked_results) with this code:

INSERT INTO ranked_results(sentence) VALUES (columnRanking()); 

I also had to create a function to delete/reset the columns in the new table upon insertion of more lines.

TRUNCATE table ranked_results RESTART IDENTITY;