Thread: help with Postgres function

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;
BEGINSELECT 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
frommy_record;if count > 0 thenw.id := my_record.id;w.domain := my_record.domain;update websites set crawl_status=2
whereid = 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!

On Wed, 16 Jun 2004, ctrl wrote:

> CREATE OR REPLACE FUNCTION getNextWebsiteForCrawl(integer) RETURNS
> website AS '
> DECLARE
> my_record RECORD;
> w website%rowtype;
> count smallint;

You can't safely use a variable named count and the count(*) expression
below I think, so you'll want to rename this variable.

> BEGIN
>  SELECT id, domain into my_record FROM websites WHERE crawl_status=1
> AND date(last_fetch) > (current_timestamp - interval ''$1 days'')

I don't think that'll get you the interval you want.  I think you wantcurrent_timestamp - $1 * interval '1 day'
instead since the $1 isn't going to get replaced inside the string.

>  ORDER BY last_fetch LIMIT 1;

>  select count(*) into count from my_record;
>  if count > 0 then

This isn't going to work either.  my_record isn't some kind of recordset,
it's a single variable so I the select count(*) from my_record doesn't
make sense.  Perhaps you could check for my_record.id being non-null.

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

If you couldn't get a value, what do you want this to return? Right now it
returns a website where the values are null I believe.

The function as written is not going to guarantee distinct results if used
from multiple transactions concurrently. How are you planning to use the
function?  Many sequential calls from a single connection, random usage
from multiple, etc?