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'; ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
