pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to retrieve N lines of a text field.
3+ messages / 3 participants
[nested] [flat]
* How to retrieve N lines of a text field.
@ 2004-01-29 15:01 Chris Travers <chris@travelamericas.com>
0 siblings, 2 replies; 3+ messages in thread
From: Chris Travers @ 2004-01-29 15:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to retrieve N lines of a text field.
@ 2004-01-29 15:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Travers <chris@travelamericas.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-01-29 15:50 UTC (permalink / raw)
To: Chris Travers <chris@travelamericas.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to retrieve N lines of a text field.
@ 2004-01-29 17:52 Joe Conway <mail@joeconway.com>
parent: Chris Travers <chris@travelamericas.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2004-01-29 17:52 UTC (permalink / raw)
To: Chris Travers <chris@travelamericas.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-29 17:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-29 15:01 How to retrieve N lines of a text field. Chris Travers <chris@travelamericas.com>
2004-01-29 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-29 17:52 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox