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

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);
IF oneline = '''' 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