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: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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. 


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

CREATE or replace FUNCTION hostname() RETURNS setof logpgsql.stats_type as
        row stats_type%ROWTYPE;
        rec record;
        newurl varchar(100);
        tempurl varchar(100);
     for rec in SELECT * from stats
                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;
LANGUAGE 'plpgsql';


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-2017 The PostgreSQL Global Development Group