Re: Using COPY

> -----Original Message-----
> From: Peter Darley [mailto:pdarley@kinesis-cem.com]
> Sent: Tuesday, May 14, 2002 3:46 PM
> To: Joel Burton
> Cc: pgsql-general@postgresql.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@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Joel Burton
> Sent: Tuesday, May 14, 2002 9:25 AM
> To: Tom Lane; joe@jwebmedia.com
> Cc: pgsql-general@postgresql.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.
>
>
>