Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with function parameters

Problem with function parameters

From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
Hi :
 
I have installed Pg 8.08
 
I create this function :
CREATE OR REPLACE FUNCTION CALCULO_VALOR_LIQUIDO_HELPDESK( nTipoDesconto SMALLINT,
                                                           nTipoComissao SMALLINT,
                                                           nDesconto NUMERIC,
                                                           nComissao NUMERIC,
                                                           nTotal NUMERIC )
RETURNS NUMERIC AS $$
 
DECLARE
nValorDesconto NUMERIC(14,2);
nValorComissao NUMERIC(12,2);
 
BEGIN
    nValorDesconto := 0;
    nValorComissao := 0;
 
    IF nTipoDesconto = 0 THEN
        nValorDesconto := nDesconto;
    ELSE
        nValorDesconto := ( nTotal * nDesconto ) / 100;
    END IF;
 
    IF nTipoComissao = 0 THEN
        nValorComissao := nComissao;
    ELSE
        nValorComissao := ( nTotal * nComissao ) / 100;
    END IF;
 
    RETURN nTotal - nValorDesconto - nValorComissao;
END;
$$ LANGUAGE plpgsql
CALLED ON NULL INPUT
SECURITY INVOKER;
 
When i test this function, i call the function with this parameters :
 
SELECT CALCULO_VALOR_LIQUIDO_HELPDESK( 0, 1, 10, 10, 1000 );
 
This is the error that happens:
 
ERROR: function calculo_valor_liquido_helpdesk(integer, integer, integer, integer, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to add explicit type casts.
 
I dont now what the two first parameters are see as integer. When 0 and 1 are true smallint values.
 
What is happeining here?
 
Thanks in advance.
 
Alejandro Michelin Salomon.

Re: Problem with function parameters

From
Richard Huxton
Date:
Alejandro Michelin Salomon ( Adinet ) wrote:
> Hi :
>
> I have installed Pg 8.08
>
> I create this function :
> CREATE OR REPLACE FUNCTION CALCULO_VALOR_LIQUIDO_HELPDESK( nTipoDesconto
> SMALLINT,
>                                                            nTipoComissao
> SMALLINT,

> When i test this function, i call the function with this parameters :
>
> SELECT CALCULO_VALOR_LIQUIDO_HELPDESK( 0, 1, 10, 10, 1000 );
>
> This is the error that happens:
>
> ERROR: function calculo_valor_liquido_helpdesk(integer, integer, integer,
> integer, integer) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You may need to
> add explicit type casts.
>
> I dont now what the two first parameters are see as integer. When 0 and 1
> are true smallint values.

"0" and "1" end up as literal integers, since you've not told it
otherwise. You have two options:
1. Cast your parameters when you call the function
   select calculo_valor_...(0::smallint, 1::smallint, ...)
2. Define your function to take integers

I'd choose #2, you're not gaining anything by having smallint parameters.

--
   Richard Huxton
   Archonet Ltd

RES: Problem with function parameters

From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
Richard Huxton wrote:
-->-----Mensagem original-----
-->De: Richard Huxton [mailto:dev@archonet.com]
-->Enviada em: sexta-feira, 1 de dezembro de 2006 12:24
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] Problem with function parameters
-->
-->
-->Alejandro Michelin Salomon ( Adinet ) wrote:
-->> Hi :
-->>
-->> I have installed Pg 8.08
-->>
-->> I create this function :
-->> CREATE OR REPLACE FUNCTION CALCULO_VALOR_LIQUIDO_HELPDESK(
-->> nTipoDesconto SMALLINT,
-->>
-->> nTipoComissao SMALLINT,
-->
-->> When i test this function, i call the function with this
-->parameters :
-->>
-->> SELECT CALCULO_VALOR_LIQUIDO_HELPDESK( 0, 1, 10, 10, 1000 );
-->>
-->> This is the error that happens:
-->>
-->> ERROR: function calculo_valor_liquido_helpdesk(integer, integer,
-->> integer, integer, integer) does not exist SQL state: 42883
-->> Hint: No function matches the given name and argument
-->types. You may need to
-->> add explicit type casts.
-->>
-->> I dont now what the two first parameters are see as
-->integer. When 0
-->> and 1 are true smallint values.
-->
-->"0" and "1" end up as literal integers, since you've not told it
-->otherwise. You have two options:
-->1. Cast your parameters when you call the function
-->   select calculo_valor_...(0::smallint, 1::smallint, ...)
-->2. Define your function to take integers
-->
-->I'd choose #2, you're not gaining anything by having
-->smallint parameters.
-->
-->--
-->   Richard Huxton
-->   Archonet Ltd
-->

I change the parameters to integer and is ok now.

Thanks for your help.

Alejandro Michelin Salomon