From: ctrl@altonsys.com
To: pgsql-sql@postgresql.org
Subject: help with Postgres function
Date: 16 Jun 2004 14:49:43 -0700
Message-ID: <46a31c4d.0406161349.38d3a34b@posting.google.com> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ctrl@altonsys.com
Subject: Re: help with Postgres function
In-Reply-To: <46a31c4d.0406161349.38d3a34b@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox