Re: Using COPY

> -----Original Message-----
> From: Peter Darley [mailto:pdarley(at)kinesis-cem(dot)com]
> Sent: Tuesday, May 14, 2002 3:46 PM
> To: Joel Burton
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: RE: [GENERAL] Using COPY
>
>
> Joel,
> Just out of curiosity, how would that query to update the marker be
> written? It's very similar to something I'd like to do, but have been
> unable to work out.
> Thanks,
> Peter Darley

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(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Joel Burton
> Sent: Tuesday, May 14, 2002 9:25 AM
> To: Tom Lane; joe(at)jwebmedia(dot)com
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Using COPY
>
> Or, if that's tricky for you to do w/the conversion/output tools
> you have at
> your disposal, turn the linebreaks into some unusual marker
> ("@@~@@") using
> any tool (emacs, perl, even Microsoft Word will do), pull the
> data in using
> COPY, and once in pgsql, you can fix this with a query.
>
>
>