Re: bug(?) : order by function limit x

pilsl(at)goldfisch(dot)at writes:
> I just face a very strange phenomena with postgres. I want to order my
> output using a userdefined function and I want to limit the output.

Bizarre. I tried to reproduce this using the following test:

create table tanzen(releasedate timestamp with time zone,
ranking integer default 3);

copy tanzen from stdin;
2002-09-23 12:45:13+02 10
2002-09-14 20:15:20+02 1
2002-09-02 12:03:43+02 10
2002-08-06 16:51:51+02 9
2002-07-06 18:07:40+02 10
2002-07-01 14:18:08+02 0
2002-06-27 18:24:08+02 0
2002-06-18 10:28:13+02 1
2002-04-21 18:12:14+02 3
\.

CREATE FUNCTION "rankval" (timestamp with time zone,integer) RETURNS interval AS
'select ($1+"interval"($2*86400)) - current_timestamp;'
LANGUAGE 'sql';

select OID,rankval(releasedate,ranking),releasedate,ranking from tanzen
order by rankval(releasedate,ranking) desc offset 0 limit 3;
select OID,rankval(releasedate,ranking),releasedate,ranking from tanzen
order by rankval(releasedate,ranking) desc offset 0 limit 8;

(I altered the function body a tad to make it acceptable to newer releases)
but this works fine for me on all versions back to and including 7.1.3.

I think there must be something you haven't told us. Can you produce
a self-contained example script that gets a wrong result?

regards, tom lane