Re: How to get normalized data from tekst column

On Nov 24, 2011, at 15:40, "Andrus Moor" <eetasoft(at)online(dot)ee> wrote:

> Harald,
>
> Thank you.
>
>> The query
>
>> SELECT id, a[1] AS name, a[2] AS percent
>> FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ', *'), '\W+') AS a
>> FROM project ) AS dummy
>
>> should work un every halfway recent PostgreSQL version - dunno about 8.1.
>
>
> I tried it but got error in 8.1:
>
> ERROR: function regexp_split_to_table(text, "unknown") does not exist
> HINT: No function matches the given name and argument types. You may need to add explicit type casts.
>
> how to get data in 8.1 ?
>
> Andrus.
>
>

You seem to have 2 options:

1. Upgrade to at least 8.4 and use the regexp functions.
2. Write something in PL/pgsql

Whether you can write a sufficient function with 8.1 features I do not know.

You main issue is you need to be able to output multiple records from a single input record and doing so before 8.4 seems problematic since functions like substring cannot do that.

David J.