Re: string parsing

Ok, this may sounds strange but ...

I have user input that is in japanese and I need to parse it. I am using
PHP and PostgreSQL but PHP's multi-byte string parsing functions don't
seem to be working (still experimental).

So my hack is to put the strings into the DB and have postgreSQL do the
parsing for me since postgreSQL doesn't seem to have any trouble
handling multi-byte strings as far as my testing as shown.

Now for the strange request ....

Is there an SQl query that will parse a string on a separator (whites
pace) and return one word per row in the result set? (I don't know *any*
perl so I can't write a PL/PGSQL function, and I'm worried that a perl
function wouldn't be multi-byte safe ...)

Sorry to ask such an OT question but I've been trying the various string
functions of pg and so far I have been able to do this ...

Thanks,

Jc

I'm tring to send a chunk of data to the postgresql backend via ODBC...

Here's a table:
CREATE TABLE testtable (
value1 int4,
value2 varchar(10)) WITH OIDS;

I'm trying to execute this a query with this intent...

"COPY testtable FROM stdin USING DELIMITERS '|';" + chr(10) +
"1|Test1" + chr(10) +
"2|Test2" + chr(10) +
"3|Test3" + chr(10) +
"4|Test4" + chr(10) +
"\." + chr(10)

Of course, this SQL statement does not work... :) I have no idea how to harness
the power of COPY without creating a file on the machine's filesystem for it to
read from, or piping the data into psql from the command line. INSERTS of the
same data go at about 1-2 per second. That gets costly if I'm doing 5000
tuples.

Any hints?

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

Jean-Christian Imbeault wrote:
> Is there an SQl query that will parse a string on a separator (whites
> pace) and return one word per row in the result set? (I don't know *any*
> perl so I can't write a PL/PGSQL function, and I'm worried that a perl
> function wouldn't be multi-byte safe ...)

In 7.2.x you could create your own C function to do this.

In 7.3beta, you could also create a PL/pgSQL function (note PL/pgSQL is not
the same as PL/Perl). For example:

CREATE OR REPLACE FUNCTION parse_words(text)
RETURNS SETOF text AS '
DECLARE
i int := 0;
word text;
BEGIN
LOOP
i := i + 1;
SELECT INTO word split_part($1, '' '', i);
IF word = '''' THEN
EXIT;
END IF;
RETURN NEXT word;
END LOOP;
RETURN;
END
' LANGUAGE 'plpgsql';

select * from parse_words('abc def hij klm');
parse_words
-------------
abc
def
hij
klm
(4 rows)

This should be multi-byte safe.

HTH,

Joe