Re: How to get normalized data from tekst column

From: David Johnston <polobo(at)yahoo(dot)com>
To: Andrus Moor <eetasoft(at)online(dot)ee>
Cc: "<hari(dot)fuchs(at)gmail(dot)com>" <hari(dot)fuchs(at)gmail(dot)com>, "<pgsql-general(at)postgresql(dot)org>" <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to get normalized data from tekst column
Date: 2011-11-24 22:26:15
Message-ID: 65ED477B-8BF8-4509-BAEA-C12DA3D9FECE@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2011-11-25 07:32:32 Re: How to get normalized data from tekst column
Previous Message Cédric Villemain 2011-11-24 22:19:23 Re: General performance/load issue