Max/min of 2 values function, plpgsql efficency?

[Karl O. Pinc]

I'd like to write:

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.)

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

Does somebody have a good solution? (I don't suppose there's
something built-in that I'm missing?)

Thanks.

Karl <kop@meme.com >
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: Max/min of 2 values function, plpgsql efficency?

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> I'd like to write:
> SELECT larger(colA, colB) FROM foo
> and am wondering the best way to go about it.[/color]
[color=blue]
> Does somebody have a good solution? (I don't suppose there's
> something built-in that I'm missing?)[/color]

All the standard datatypes have built-in two-argument larger()
functions, though they're generally named something more obscure
than that; try \df *larger*. The MAX and MIN aggregates require
larger() and smaller() functions --- if you can't find the function
you want by name, look into pg_aggregate to see what the transition
function for the relevant aggregate is.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Joe Conway]

Re: Max/min of 2 values function, plpgsql efficency?

Karl O. Pinc wrote:[color=blue]
> 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.)[/color]

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 :-(
[color=blue]
> Are there significant performance penalities if I were to use a
> a homemade plpgpgql function?[/color]

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(anyele ment,
anyelement) returns anyelement as ''''select case when $1 > $2 then $1
else $2 end'''' language ''''sql'''''';
v_part1 text := ''create or replace function greatest(anyele ment'';
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,1 0,1234,2,3,4,5, 66,7,8,9,10,1,2 7,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,1 0,1234,2,3,4,5, 66,7,8,9,10,1,2 7,3,4,5,6,347,8 ,9,10,1,2);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.0 1 rows=1 width=0) (actual time=0.006..0.0 07
rows=1 loops=1)
Total runtime: 0.039 ms
(2 rows)

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

HTH,

Joe



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match