pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Second maximum value
2+ messages / 2 participants
[nested] [flat]

* Second maximum value
@ 2003-04-27 10:37 Marco Bubke <marco@bubke.de>
  2003-04-27 16:12 ` Re: Second maximum value Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Marco Bubke @ 2003-04-27 10:37 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Second maximum value
  2003-04-27 10:37 Second maximum value Marco Bubke <marco@bubke.de>
@ 2003-04-27 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2003-04-27 16:12 UTC (permalink / raw)
  To: Marco Bubke <marco@bubke.de>; +Cc: pgsql-sql

Marco Bubke <marco@bubke.de> writes:
> create type counter_max as (
> maximum real,
> value int );

> 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;

I think at the moment you need to say

	result counter_max%rowtype;

Otherwise plpgsql thinks "result" has scalar type and doesn't do the
right things.

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-04-27 16:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-27 10:37 Second maximum value Marco Bubke <marco@bubke.de>
2003-04-27 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>

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