Обсуждение: 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