Re: Max/min of 2 values function, plpgsql efficency? - Mailing list pgsql-general

Karl O. Pinc wrote:
> SELECT larger(colA, colB) FROM foo
>
> and am wondering the best way to go about it.
>
> (Really, I'd like the larger() function to take an arbitrary
> number of arguments but I don't see how to do that.)

See below -- the function was actually posted in July of last year, but
doesn't seem to have made it into the mail archives for some reason :-(

> Are there significant performance penalities if I were to use a
> a homemade plpgpgql function?

But the rest of the thread is there, and discusses that issue -- see
this message:

   http://archives.postgresql.org/pgsql-sql/2003-07/msg00040.php


--8<--------------------------------------------------------
create or replace function make_greatest() returns text as '
declare
   v_args int := 32;
   v_first text := ''create or replace function greatest(anyelement,
anyelement) returns anyelement as ''''select case when $1 > $2 then $1
else $2 end'''' language ''''sql'''''';
   v_part1 text := ''create or replace function greatest(anyelement'';
   v_part2 text := '') returns anyelement as ''''select greatest($1,
greatest($2'';
   v_part3 text := ''))'''' language ''''sql'''''';
   v_sql text;
begin
   execute v_first;
   for i in 3 .. v_args loop
     v_sql := v_part1;
     for j in 2 .. i loop
       v_sql := v_sql || '',anyelement'';
     end loop;

     v_sql := v_sql || v_part2;

     for j in 3 .. i loop
       v_sql := v_sql || '',$'' || j::text;
     end loop;

     v_sql := v_sql || v_part3;

     execute v_sql;
   end loop;
   return ''OK'';
end;
' language 'plpgsql';

select make_greatest();

--8<--------------------------------------------------------

Now you should have 31 "greatest" functions, accepting from 2 to 32
arguments. *Not* heavily tested, but seemed to work for me.

regression=# select
greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2);
  greatest
----------
      1234
(1 row)

regression=# explain analyze select
greatest(112,2,3,4,5,6,7,8,9,10,1234,2,3,4,5,66,7,8,9,10,1,27,3,4,5,6,347,8,9,10,1,2);
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.007
rows=1 loops=1)
  Total runtime: 0.039 ms
(2 rows)

All of this assumes you are on 7.4.x though.

HTH,

Joe