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

substring syntax with regexp

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-general(at)postgresql(dot)org
Subject: substring syntax with regexp
Date: 2004-06-30 13:45:18
Message-ID: 20040630084518.A21531@hovey.hoveymotorcars.com (view raw or flat)
Thread:
Lists: pgsql-general
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:


the file 'hostname.sql' is pl/pgsql


main=> \e hostname.sql
ERROR:  syntax error at or near "http" at character 290
LINE 13:   newuri = substr(tempuri from 'http://[^/]*/.*');
                                         ^
I don't know, the docs are:

The substring function with two parameters, substring(string from pattern), provides extraction of a substring that matches a POSIX regular expression pattern. 

(http://www.postgresql.org/docs/current/static/functions-matching.html)


the full 'hostname.sql' as is now is:  

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://[^/]*/.*');
                row.uri = newurl;
                row.r = rec.r;
                return next row;
        end loop;
        return next row;
        return;
end
'
LANGUAGE 'plpgsql';


Responses

pgsql-general by date

Next:From: joseph speigleDate: 2004-06-30 13:48:27
Subject: Re: mysql2postgres has no documentation?
Previous:From: Shridhar DaithankarDate: 2004-06-30 13:15:39
Subject: Re: DML Restriction unless through a function

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