Re: substring syntax with regexp - Mailing list pgsql-general

joseph speigle wrote:
> hi,
>
> Does anybody know offhand what is the correct way to use substr to
> extract the domain name from a client_referer column as logged by
> mod_pgsqllog (httpd module), by correcting the following:

You have a quoting problem

> CREATE or replace FUNCTION hostname() RETURNS setof
> logpgsql.stats_type as ' declare row stats_type%ROWTYPE; rec record;
> newurl varchar(100); tempurl varchar(100); begin for rec in SELECT *
> from stats loop row.c = rec.c; tempurl = rec.url; newuri =
> substr(tempuri from 'http://[^/]*/.*');

OK, you probably want to use := for assignment. Also, you're already
inside one set of quotes, so you'll need to escape the quotes for your
string.

     newuri := substr(tempuri from ''http://[^/]*/.*'');
or
     newuri := substr(tempuri from \'http://[^/]*/.*\');

--
   Richard Huxton
   Archonet Ltd