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
