Re: Creating a function that acept any data type

On Fri, Mar 10, 2006 at 05:12:53PM -0300, Alejandro Michelin Salomon ( Adinet ) wrote:
> I am working in a migration. Im am migrating systems based in mysql to
> postgresql.
>
> I am trying to create a function named IFNULL, to not migrate any ocurrence
> of this mysql function in my code.
>
> The IFNULL function is the same of COALESCE in postgresql.

Are you aware of the MySQL Compatibility Functions module?  It has
IFNULL.

http://pgfoundry.org/projects/mysqlcompat/
http://software.newsforge.com/article.pl?sid=05/12/15/1611251&from=rss

> This code does not work.
>
> CREATE OR REPLACE FUNCTION IFNULL( xValor ANY, xPadrao ANY )
> RETURNS ANY AS $$

Change ANY to ANYELEMENT and the code should work.  And for something
this simple you could use an SQL function:

CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
RETURNS anyelement AS $$
  SELECT COALESCE($1, $2);
$$ LANGUAGE sql IMMUTABLE;

You'll have to cast one of the arguments if their types can't be
determined.

test=> SELECT ifnull('abc', 'xyz');
ERROR:  could not determine anyarray/anyelement type because input has type "unknown"

test=> SELECT ifnull('abc', 'xyz'::text);
 ifnull
--------
 abc
(1 row)

test=> SELECT ifnull(NULL, 'xyz'::text);
 ifnull
--------
 xyz
(1 row)

--
Michael Fuhr