Обсуждение: function with unknown params

i've declared and implemented e function like:

CREATE OR REPLACE FUNCTION acquistoLotto(m_g INTEGER, grossista VARCHAR(20),
produttore BIGINT, costo INTEGER, dat DATE, descr VARCHAR(120), num_prod
INTEGER) RETURNS VOID AS $$

but when i'm trying to call it like

select acquistoLotto(0, 'grossista', 52187073424, 10, 22/1/2013, 'ciao ciao
ciao', 10);

an error occours:

ERROR:  function acquistolotto(integer, unknown, bigint, integer, integer,
unknown, integer) does not exist

How it's possible that the second and the sixth args it's unknown type? i've
create another function that takes varchar as  args and call in the same way
and no errors occours.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/function-with-unknown-params-tp5763215.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

giozh wrote:
> i've declared and implemented e function like:
>
> CREATE OR REPLACE FUNCTION acquistoLotto(m_g INTEGER, grossista VARCHAR(20),
> produttore BIGINT, costo INTEGER, dat DATE, descr VARCHAR(120), num_prod
> INTEGER) RETURNS VOID AS $$
>
> but when i'm trying to call it like
>
> select acquistoLotto(0, 'grossista', 52187073424, 10, 22/1/2013, 'ciao ciao
> ciao', 10);

You need to enclose the date in quotes, too.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

ok, it works. But why on error message i had that two unknown data type? if
was an error on date type, why it don't signal that?



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/function-with-unknown-params-tp5763215p5763224.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

giozh <giozh@yahoo.it> wrote:

> ok, it works. But why on error message i had that two unknown
> data type? if was an error on date type, why it don't signal
> that?

Because PostgreSQL treats a quoted literal as being of unknown type
-- each of these could have matched a parameter of any type, and
would have been treated as a literal of the needed type.  On the
other hand, if you match up the parameters of *known* types against
the function's signature, you will see that the fifth parameter is
declared to be a date, but the value you supplied is an integer --
because 22 divided by 1 divided by 2013 evaluates to an integer of
zero.  Since there can be many different functions with any given
name, with different parameter types, it doesn't try to match up
parameters and guess which one might be the wrong type -- for all
it knows the problem is that there's a missing function with a
signature compatible with the values you specified.  It tells you
the types it sees.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company