Thread: substring syntax with regexp

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';

On Wed, 30 Jun 2004 08:45:18 -0500, joseph speigle <joe.speigle@jklh.us> wrote:
> hi,
>
> Does anybody know offhand what is the correct way to use substr to extract the domain name from a client_referer
columnas 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://[^/]*/.*');

You have several immediate problems with this line:
- your regex should be double-quoted;
- the relevant function is "substring", not "substr";
- the regex pattern you want is probably more like :
   'http://([^/]*)/', e.g.

test=> select substring('http://www.example.com/dir/file.html' from
'http://([^/]*)');
  substring
-------------
 www.example.com
(1 row)

HTH

Ian Barwick

On Wed, Jun 30, 2004 at 08:45:18AM -0500, 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:

Nah, your problem is before the syntax of the substr function.  You have
to quote your single quotes in the function text.  So the relevant like
would look like

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

I didn't look at the rest of the function ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Uno combate cuando es necesario... ¡no cuando está de humor!
El humor es para el ganado, o para hacer el amor, o para tocar el
baliset.  No para combatir."  (Gurney Halleck)

On Wed, 30 Jun 2004, joseph speigle wrote:

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

You probably need to double those quotes ('') since it's inside
a quoted string (the function body).

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

joseph speigle <joe.speigle@jklh.us> writes:
> CREATE or replace FUNCTION hostname() RETURNS setof logpgsql.stats_type as
> '
> declare
> ...
>                 newuri = substr(tempuri from 'http://[^/]*/.*');

You forgot about doubling quotes within a function body :-(

            regards, tom lane

hi,
thanks everybody.  It was a combination of:

changing function name from substr to substring
double-quoting inside the function
using select into inside the plpgsql function to retrieve the substring


-- retrieve hostname from the client_referrer field
CREATE OR REPLACE FUNCTION hostname() RETURNS setof modlog.stats_type AS
'
DECLARE
  row stats_type%ROWTYPE;
  rec record;
  newclient_referrer varchar(100);
BEGIN
   FOR rec IN SELECT * FROM stats ORDER BY uri_hits DESC
   LOOP
    row.uri_hits = rec.uri_hits;
    SELECT INTO row.client_referrer SUBSTRING(rec.client_referrer FROM ''http://([^/]*).*'');
    IF row.client_referrer IS NULL THEN
      -- /cgi-bin/404.pl etc.
       row.client_referrer := ''localhost'';
    END IF;
    row.uri = NULL;
    RETURN NEXT row;
  END LOOP;
  RETURN NEXT ROW;
  RETURN;
END
'
LANGUAGE 'plpgsql';
select * from hostname();