[SQL] help with Postgres function

[ctrl]

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!

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] best method to copy data across databases

[ctrl]

I need to copy data that I have on a table in one Postgres database
into another table, on a different database. The destination table is
not identical with the source table so I need to do some mapping
between the 2 tables.
What would be the best (and quickest) way to transfer the data? (there
are over 500k rows)

thanks!

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] best method to copy data across databases

[ctrl]

Many thanks Tony and Tom,

since this was a "one time" process speed wasn't an issue...
I just did a plain pg_dump with insert and explicit column names in
the dump, then used vi to rename the columns and get rid of some of
them...
non very scientific but it worked :)

cheers.

[EMAIL PROTECTED] (Tom Lane) wrote in message news:<[EMAIL PROTECTED]>...
> [EMAIL PROTECTED] (Tony Reina) writes:
> > If the 2 tables have different arrangements, then I'm not sure if
> > there is a quick way. The safest way is probably to do a pg_dump
> > --attribute-inserts.
> 
> In recent versions (definitely 7.4, don't remember about 7.3),
> pg_dump will include a column list in its COPY commands, so a
> plain pg_dump should work.  The way with COPY will be a good bit
> faster than a pile of INSERT commands.
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
>   joining column's datatypes do not match

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match