In case anyone else was interested in this issue: I hadn't fully
understood the power of the fact that min(int4,int4) was a different
function from min(int4,int4,int4). It's not exactly an implementation
of an indeterminate number of arguments, but I used the feature to make
min() work for any number of arguments up to 6. The method is obviously
extensible further, but 6 is all I need for the moment. It's still ugly,
but maybe slightly less so than Ansley's kind solution. Here's the SQL
code:
CREATE FUNCTION min(int4, int4)
RETURNS int4
AS 'BEGIN IF $1 > $2 THEN RETURN $2; ELSE RETURN $1; END IF; END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4)
RETURNS int4
AS 'BEGIN RETURN min($1, min($2, $3)); END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN RETURN min(min($1,$2),min($3,$4)); END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN RETURN min($1,min($2,$3),min($4,$5)); END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION min(int4,int4,int4,int4,int4,int4)
RETURNS int4
AS 'BEGIN RETURN min(min($1,$2),min($3,$4),min($5,$6)); END;'
LANGUAGE 'plpgsql';
> Michael Ansley wrote:
>
> Really ugly, but you could cast to string and concatenate with commas:
>
> minimum(arg1 || ',' || arg2 || ',' || arg3 || ',' || arg4 || ',' ||
> arg5)
>
> i.e.:
>
> create function minimum(text) returns integer
>
> and then do the parsing internally ('specially if you're using perl).
> Pretty bad, but it's an option ;-)
>
> -----Original Message-----
> From: Andrew Perrin [mailto:aperrin@socrates.berkeley.edu]
> Sent: 26 February 2001 05:05
> To: pgsql-sql@postgresql.org
> Subject: [SQL] create function w/indeterminate number of args?
>
> Greetings.
>
> I find myself in need of a minimum() function. This is different from
> the
> min() function, which operates across records; what I need is to be
> able
> to say:
>
> UPDATE foo SET newcol = minimum(field1, field2, field3, field4);
>
> From what I can tell there is no such beast built in, but I would be
> happy
> to be proved wrong.
>
> Anyway... I can write such a function easily enough in perl, probably
> something like:
>
> my $min=$_[0];
> $min > $_ and $min = $_ foreach @_;
> return $min;
>
> but I can't determine how to allow varying numbers of arguments to be
> passed to a function. That is, sometimes I need minimum(arg1, arg2)
> but
> sometimes it's minimum(arg1, arg2, arg3, arg4, arg5), etc.
>
> Thanks-
> Andy Perrin
>
> ----------------------------------------------------------------------
>
> Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
> Chapel Hill, North Carolina, USA - http://demog.berkeley.edu/~aperrin
> aperrin@socrates.berkeley.edu - aperrin@igc.apc.org
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> Nick West - Global Infrastructure Manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **********************************************************************
--
--------------------------------------------------------------
Andrew J. Perrin - Programmer/Analyst, Desktop Support
Children's Primary Care Research Group, UNC - Chapel Hill
(919)966-9394 * andrew_perrin@unc.edu
