Message-ID: <3EABB301.5040000@bubke.de>
Date: Sun, 27 Apr 2003 12:37:53 +0200
From: Marco Bubke <marco@bubke.de>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Second maximum value
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hello

I'm new to postgres and I have tried to write a function which returns 
the second(third...) maximum to use it in a aggregation. But it's not 
function and I have no clue whats wrong.

Here what I do:

create type counter_max as (
maximum real,
value int );

create function get_counter_max(real, int) returns counter_max as 
'select $1, $2' language sql;

create or replace function x_max(counter_max, real) returns counter_max as '
declare
    maxi alias for $1;
    value alias for $2;
    result counter_max;
  begin
    if maxi.counter = 0 then
    return maxi;
    elsif value >= maxi.maximum then
      result.maximum := value;
      result.counter := maxi.counter -1;
      return result;
    else
      return maxi;
    end if;
  end;
  ' language 'plpgsql';

When I execute:  select x_max(get_counter_max(1., 1), 2.::real);
  it says:

WARNING:  plpgsql: ERROR during compile of x_max near line 11
ERROR:  return type mismatch in function returning tuple at or near "result"

Thanks in advance

Marco