pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with Postgres function
2+ messages / 2 participants
[nested] [flat]
* help with Postgres function
@ 2004-06-16 21:49 ctrl <ctrl@altonsys.com>
0 siblings, 1 reply; 2+ messages in thread
From: ctrl@altonsys.com @ 2004-06-16 21:49 UTC (permalink / raw)
To: 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!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: help with Postgres function
@ 2004-06-21 05:35 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: ctrl <ctrl@altonsys.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2004-06-21 05:35 UTC (permalink / raw)
To: ctrl <ctrl@altonsys.com>; +Cc: pgsql-sql
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 want
current_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?
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-06-21 05:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-16 21:49 help with Postgres function ctrl <ctrl@altonsys.com>
2004-06-21 05:35 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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