Thread: How to retrieve N lines of a text field.

Hi all;

This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue.  I need to retrieve the first N lines of
a text field.  N would be assigned using a parameterized query, if possible.

I had thought about using something like:
select substring(test from '#"' || repeat('%\n', $1) || '#"%' for '#') from
multiline_test;
However, this always selects every line but the final one (because %\n seems
to be interpreted to be the largest possible string, while I want it to be
the smallest possible string).

Is there a workaround?  Any other help?  Or do I need to write a UDF?

Best Wishes,
Chris Travers

"Chris Travers" <chris@travelamericas.com> writes:
> However, this always selects every line but the final one (because %\n seems
> to be interpreted to be the largest possible string, while I want it to be
> the smallest possible string).

I don't think you can solve this in SQL99 regular expressions, but
if you use the POSIX-style regex operators, you can write something
like "[^\n]*\n" to match exactly one line.  There are other features
that might help too, such as the counted-match operator "{n}".
Read the man page.

BTW, I bought a copy of the O'Reilly book "Mastering Regular
Expressions" a couple years ago, and it is worth every penny
if you do much of anything with regexes.
        regards, tom lane

Chris Travers wrote:
> This is a complex issue, and i am tryign to figure out how to use regular
> expressions to resolve this issue.  I need to retrieve the first N lines of
> a text field.  N would be assigned using a parameterized query, if possible.

How 'bout something like this:

CREATE OR REPLACE FUNCTION first_n_lines(text, int)
RETURNS setof text AS '
DECLARE  i int := 0;  oneline text;
BEGIN  LOOP    i := i + 1;    IF i > $2 THEN      EXIT;    END IF;    SELECT INTO oneline split_part($1, ''\n'', i);
IFoneline = '''' THEN      EXIT;    END IF;    RETURN NEXT oneline;  END LOOP;  RETURN;
 
END
' LANGUAGE 'plpgsql';

regression=# select * from first_n_lines('abc\ndef\nghi', 2); first_n_lines
--------------- abc def
(2 rows)

HTH,

Joe