Re: Smallint needs explicit cast in psql?

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