Re: substring syntax with regexp

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();