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

help with Postgres function

From: ctrl(at)altonsys(dot)com (ctrl)
To: pgsql-sql(at)postgresql(dot)org
Subject: help with Postgres function
Date: 2004-06-16 21:49:43
Message-ID: 46a31c4d.0406161349.38d3a34b@posting.google.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hello.
I'd appreciate any help with a function I'm writing.
I have a simple table like this:
CREATE TABLE websites ( 
id BIGSERIAL not null primary key,
domain character varying(256) NOT NULL,
last_fetch timestamp without time zone DEFAULT 'now()',
crawl_status smallint not null DEFAULT 1
);

and my function retrieves the next available row in this table, by age
(the oldest inserted and with status 1)
I have written this but I have problems with it...and also it doesn't
seems right to me...from coding style and performance point of view.

CREATE OR REPLACE FUNCTION getNextWebsiteForCrawl(integer) RETURNS
website AS '
DECLARE
my_record RECORD;
w website%rowtype;
count smallint;
BEGIN
 SELECT id, domain into my_record FROM websites WHERE crawl_status=1
AND date(last_fetch) > (current_timestamp - interval ''$1 days'')
 ORDER BY last_fetch LIMIT 1;
 select count(*) into count from my_record;
 if count > 0 then
 w.id := my_record.id;
 w.domain := my_record.domain;
 update websites set crawl_status=2 where id = my_record.id;
 end IF;
 return w;
END;
' LANGUAGE plpgsql;

The reason I have that IF is for when there are no more rows
available...
when I call this function (SELECT * FROM getNextWebsiteForCrawl(5))
I get an error:
WARNING:  Error occurred while executing PL/pgSQL function
getnextwebsiteforcrawl
WARNING:  line 8 at select into variables
ERROR:  parser: parse error at or near "(" at character 13

Could somebody good show me how to do it better?
Thanks a lot!

Responses

pgsql-sql by date

Next:From: Jie LiangDate: 2004-06-17 00:53:07
Subject: Re: Prepare Statement
Previous:From: Phil EndecottDate: 2004-06-16 15:12:27
Subject: Inheritance, plpgsql inserting, and revisions

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