Re: create function w/indeterminate number of args? - Mailing list pgsql-sql

From Andrew Perrin
Subject Re: create function w/indeterminate number of args?
Date
Msg-id 3A9D0FDD.8D2F4579@unc.edu
Whole thread Raw
In response to create function w/indeterminate number of args?  (Andrew Perrin <aperrin@socrates.berkeley.edu>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Blaise Carrupt
Date:
Subject: Help creating rules/triggers/functions
Next
From: Justin Clift
Date:
Subject: Help needed -> ERROR: record arow has no field description