Postgres Pro
Downloads
Home   >   mailing lists

Thread: Creating a function that acept any data type

Creating a function that acept any data type

From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
Hi :

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.

This code does not work.

CREATE OR REPLACE FUNCTION IFNULL( xValor ANY, xPadrao ANY )
RETURNS ANY AS $$

BEGIN
    RETURN COALESCE( xValor, xPadrao );
END;
$$ LANGUAGE plpgsql
CALLED ON NULL INPUT
SECURITY INVOKER;

Can you help-me to create a function that acept any data type ?

Thank in advance

Alejandro Michelin Salomon
Porto Alegre
Brasil

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/278 - Release Date: 9/3/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.1/278 - Release Date: 9/3/2006

Re: Creating a function that acept any data type

From
Michael Fuhr
Date:
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

Re: Creating a function that acept any data type

From
Tom Lane
Date:
"Alejandro Michelin Salomon \( Adinet \)" <alejmsg@adinet.com.uy> writes:
> Can you help-me to create a function that acept any data type ?

Use ANYELEMENT, not ANY.  Also I'd suggest making it a SQL function not
a plpgsql function, so that it can be inlined.

            regards, tom lane

RES: Creating a function that acept any data type

From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
Thanks Michael and Tom.

Y try put anyelement and the function works perfectly.

Alejandro Michelin Salomon
Porto Alegre
Brasil


-----Mensagem original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Em nome de Michael Fuhr
Enviada em: sexta-feira, 10 de março de 2006 17:43
Para: Alejandro Michelin Salomon ( Adinet )
Cc: Pgsql-General
Assunto: Re: [GENERAL] 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/3/2006


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/3/2006


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.4/282 - Release Date: 15/3/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.4/282 - Release Date: 15/3/2006