Skip site navigation (1) Skip section navigation (2)

Re: substring syntax with regexp

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: substring syntax with regexp
Date: 2004-06-30 16:10:15
Message-ID: 20040630111015.A21987@hovey.hoveymotorcars.com (view raw or flat)
Thread:
Lists: pgsql-general
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();



In response to

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2004-06-30 16:29:18
Subject: Re: backups
Previous:From: Bruno Wolff IIIDate: 2004-06-30 16:09:48
Subject: Re: DML Restriction unless through a function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group