Обсуждение: Smallint needs explicit cast in psql?

Matt Musgrove <MMusgrove@efji.com> writes:
> Does psql require smallints to be explicitly cast as such?

Undecorated integer-looking constants are taken as integer, bigint, or
numeric depending on size.  Not smallint.  By and large it's best
not to use smallint as the declared argument type of a function unless
you really have to.

(We have looked into allowing small-enough literals to be taken as
smallint.  Turns out to break a whole lot of stuff, by introducing
ambiguities where there were none before, so it's not likely
to ever happen.)

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> By and large it's best not to use smallint as the declared argument type of a function unless you really have to.

Thank you for the quick response.

Is this documented? If so, where? (I couldn't find it.) If not, how does one go about updating the documentation?

Thanks,
Matt

Matt Musgrove <MMusgrove@efji.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> By and large it's best not to use smallint as the declared argument type of a function unless you really have to.

> Thank you for the quick response.

> Is this documented? If so, where?

Well, the initial typing of numeric constants is documented in
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
under 4.1.2.6 Numeric Constants.

The fact that the integer->smallint coercion is assignment and not
implicit is probably not stated anywhere in so many words, but there are
at least a couple of places that say that down-casts to more restricted
types are not normally applied implicitly.  (The only one I remember
offhand is in the CREATE CAST reference page, but I think it's explained
in the main text someplace, possibly in chapter 10.)  In practice I
think people would look into the catalogs, eg with psql's \dC, if they
wanted that particular detail.

I don't believe we say anywhere that "functions with smallint arguments
are a bad idea", if that's what you were looking for.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Well, the initial typing of numeric constants is documented in
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
under 4.1.2.6 Numeric Constants.

I hadn't read that prior to posting (smallint isn't mentioned and that's what I was searching for).

> The fact that the integer->smallint coercion is assignment and not implicit is probably not stated anywhere in so
manywords, but there are at least a couple of places that say that down-casts to more restricted types are not normally
appliedimplicitly.  (The only one I remember offhand is in the CREATE CAST reference page, but I think it's explained
inthe main text someplace, possibly in chapter 10.)   

I've read all of chapter 10 a few times now but I'll re-read it again.

Tom Lane <tgl@sss.pgh.pa.us> writes:
> In practice I think people would look into the catalogs, eg with psql's \dC, if they wanted that particular detail.

I think that you are giving us novices too much credit. The thought never crossed our minds here at work. I looked at
itafter you mentioned it and see how it can be useful. 

It still doesn't make sense to the two of us here at work. Perhaps if I show you a slightly expanded example you'll
understandwhy we are so confused. We have a table with a smallint column and we can do inserts without problems. (Based
onwhat you've said in this thread, I would expect the inserts to fail.) We then added a function to do the insert but
thecall fails. 

[mmusgrove@nmsdev2 mm-nms_4.5 template (security33)]$ psql -d nms
psql (9.1.4)
Type "help" for help.

nms=# CREATE TABLE foo ( a smallint );
CREATE TABLE
nms=# \d foo
      Table "public.foo"
 Column |   Type   | Modifiers
--------+----------+-----------
 a      | smallint |

nms=# CREATE OR REPLACE FUNCTION test_smallint(
    parm    smallint
) RETURNS VOID AS $$
BEGIN
    INSERT INTO foo ( a ) values ( parm );
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
nms=# \df test_smallint
                            List of functions
 Schema |     Name      | Result data type | Argument data types |  Type
--------+---------------+------------------+---------------------+--------
 public | test_smallint | void             | parm smallint       | normal
(1 row)

nms=# CREATE OR REPLACE FUNCTION test_int(
    parm    integer
) RETURNS VOID AS $$
BEGIN
    INSERT INTO foo ( a ) values ( parm );
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
nms=# \df test_int
                          List of functions
 Schema |   Name   | Result data type | Argument data types |  Type
--------+----------+------------------+---------------------+--------
 public | test_int | void             | parm integer        | normal
(1 row)

nms=# insert into foo ( a ) values ( 1 );
INSERT 0 1
nms=# select test_smallint(2);
ERROR:  function test_smallint(integer) does not exist
LINE 1: select test_smallint(2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
nms=# select test_smallint( parm := 2 );
ERROR:  function test_smallint(parm := integer) does not exist
LINE 1: select test_smallint( parm := 2 );
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
nms=# select test_int(3);
 test_int
----------

(1 row)

nms=# select * from foo;
 a
---
 1
 3
(2 rows)

PostgreSQL knows that foo.a is a smallint. PostgreSQL knows that test_smallint takes a parameter parm that is a
smallint.So why does the insert work but the function call fail? Shouldn't both cases behave the same way? Shouldn't
theyeither both work as is or both fail until given an explicit cast? It seems like the call to the test_smallint
functionshould cause a downcast during the assignment to parm. 

Thanks for your patience,
Matt

Matt Musgrove <MMusgrove@efji.com> writes:
> It still doesn't make sense to the two of us here at work. Perhaps if
> I show you a slightly expanded example you'll understand why we are so
> confused. We have a table with a smallint column and we can do inserts
> without problems.

Right, because assigning to a table column is an "assignment" cast ---
which has more liberal rules, because either you can coerce the value
to the table column's type or you're going to fail anyway.  So the
system will take the integer literal and try to downcast to smallint,
and if that doesn't work then the query was doomed anyhow.

Coercion of a value to match a function argument is a different matter;
the system will not choose a coercion that is "surprising" in any way
or has the potential to fail due to issues such as range limitations.
The reason for the difference in behavior is probably not very obvious
when you have only one possible function to match, but we need this
restriction in order for things to behave sanely with overloaded
functions (or operators).  The text in chapter 10 expands on that a
bit.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:
> Right, because assigning to a table column is an "assignment" cast ---
> which has more liberal rules, because either you can coerce the value
> to the table column's type or you're going to fail anyway.  So the
> system will take the integer literal and try to downcast to smallint,
> and if that doesn't work then the query was doomed anyhow.

This one sank in last night when I re-read the docs for CREATE CAST.

> Coercion of a value to match a function argument is a different matter;
> the system will not choose a coercion that is "surprising" in any way
> or has the potential to fail due to issues such as range limitations.
> The reason for the difference in behavior is probably not very obvious
> when you have only one possible function to match, but we need this
> restriction in order for things to behave sanely with overloaded
> functions (or operators).  The text in chapter 10 expands on that a
> bit.

This is exactly what was confusing us; we aren't using function overloading so at the time it seemed like it should
justwork. I think I finally understand it again re-reading chapter 10 again. 

Thanks,
Matt