Re: text parsing function

On Apr 18, 2005, at 11:41 AM, Keith Worthington wrote:

> I am trying to create software that will parse a string of
> varchar(160) and
> write different parts of it into various locations in a database.
>
> My thought process is something like this.
> Create one or more functions to parse the data.
> Create a trigger function that calls parsing function(s) and writes
> the data
> into the appropriate locations.
> Create after trigger on table that contains the entire string.

You could also use the COPY command to copy the unparsed data to a
temporary table (one text column) and then call a function to parse the
rows in the temporary table. The pgEdit distribution has an example of
this for processing HTTP logs.

>
> I have written the first function using pl/pgsql. As one might expect
> text
> processing in pl/pgsql is messy.

>
> Unfortunately I do not know Perl which is probably better suited to
> the text
> munging part of my problem. Is there any penalty for using pl/pgsql
> for this
> function? Given the inputs below does anyone see a problem with my
> code? Can
> anyone suggest a better course of action?

I have not done any benchmarks or optimization, but text processing in
psql seems relatively slow.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL