Re: Using COPY

Well, you could, with something like

UPDATE tblFoo SET foo=
substring(foo from 1 for (position('@@~@@' in foo))-1) || '\n' ||
substring(foo from (position('@@~@@' in foo))+5) where foo like '%@@~@@%';

(Try saying that ten times fast! :) )

P.S. Fix the +5 above to match the length of the marker string.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter Darley
> Sent: Tuesday, May 14, 2002 6:15 PM
> To: Joel Burton
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using COPY
>
>
> Joel,
>     Darn, I was hoping it was possible to do it with just SQL. :)
> Thanks,
> Peter Darley
>
> -----Original Message-----
> From: Joel Burton [mailto:joel@joelburton.com]
> Sent: Tuesday, May 14, 2002 3:11 PM
> To: Peter Darley
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Using COPY
>
> create function fix_marker(text) returns text as '
>   $_ = shift;
>   s/@@~@@/\\n/;
>   return $_;
> ' language 'plperl';
>
> update tblFoo set foo=fix_marker(foo) where foo like '%@@~@@%';
>
> would do nicely. Or you could do it in plpython, plruby (if installed), or
> plperl.
>
> You could do it in plpgsql with a combination of ugly left || right with
> strpos, but it's a lot easier in languages that have string-replace
> functions.
>
> - J.
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>