473,287 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Max/min of 2 values function, plpgsql efficency?

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 <ko*@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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
2 7159
"Karl O. Pinc" <ko*@meme.com> writes:
I'd like to write:
SELECT larger(colA, colB) FROM foo
and am wondering the best way to go about it. Does somebody have a good solution? (I don't suppose there's
something built-in that I'm missing?)


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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2
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...7/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

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

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Zitan Broth | last post by:
Greetings All, Running pg 7.3.4 and was reading: http://archives.postgresql.org/pgsql-interfaces/2003-09/msg00018.php . Basically want to assign values to an array and then a 2d array. However...
1
by: Julie May | last post by:
I have 90% of my function working and I know to get the next 10% it is justa matter of getting the quotations and the escaping quotations correct. Here is the portion that does work: <working...
5
by: Thomas LeBlanc | last post by:
I copied an example from the help: CREATE FUNCTION somefunc() RETURNS integer AS ' DECLARE quantity integer := 30; BEGIN RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 30...
10
by: lnd | last post by:
After copied pg database from one PC to another -I could not find plpgsql function(s) in the copied database. -had to instal plpgsql language handler again -whilst tables and data moved...
14
by: Karl O. Pinc | last post by:
Hi, Thought perhaps some other eyes than mine can tell if I'm doing something wrong here or if there's a bug somewhere. I've never passed a ROWTYPE varaible to a function but I don't see where...
0
by: Jeff Boes | last post by:
I hope this helps someone else ... I had struggled some time ago with attempts to get a rank of values query to work, but then I gave up and set it aside. I had another reason to attack it, and in...
0
by: Jeff Boes | last post by:
Gah, I'm dreadfully sorry. The original functions were cut-and-pasted from a "\df+" window, which meant they lost their quoted-ness, which means if you try to cut and paste from my message to a SQL...
2
by: Mark Cave-Ayland | last post by:
Hi everyone, I'm trying to write a recursive plpgsql function in PostgreSQL 7.4.2 that given a tree node id (ictid) will return all the nodes below it in the tree, one row per node. When I try...
9
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.