From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: ctrl <ctrl@altonsys.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: help with Postgres function
Date: Sun, 20 Jun 2004 22:35:09 -0700 (PDT)
Message-ID: <20040620221537.C69900@megazone.bigpanda.com> (raw)
In-Reply-To: <46a31c4d.0406161349.38d3a34b@posting.google.com>
References: <46a31c4d.0406161349.38d3a34b@posting.google.com>
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?
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: sszabo@megazone.bigpanda.com, ctrl@altonsys.com
Subject: Re: help with Postgres function
In-Reply-To: <20040620221537.C69900@megazone.bigpanda.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