Skip site navigation (1) Skip section navigation (2)

Re: substring syntax with regexp

From: Richard Huxton <dev(at)archonet(dot)com>
To: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: substring syntax with regexp
Date: 2004-06-30 15:56:58
Message-ID: 40E2E2CA.5000601@archonet.com (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-06-30 16:00:44
Subject: Re: DML Restriction unless through a function
Previous:From: Stephan SzaboDate: 2004-06-30 15:55:13
Subject: Re: substring syntax with regexp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group