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