Re: bug(?) : order by function limit x - Mailing list pgsql-general

pilsl@goldfisch.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