substring syntax with regexp - Mailing list 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
aslogged 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
matchesa 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';