Re: Smallint needs explicit cast in psql?

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 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.)

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 it after 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 understand why we are so confused. We have a table with a smallint column and we can do inserts without problems. (Based on what you've said in this thread, I would expect the inserts to fail.) We then added a function to do the insert but the call fails.

[mmusgrove(at)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 they either both work as is or both fail until given an explicit cast? It seems like the call to the test_smallint function should cause a downcast during the assignment to parm.

Thanks for your patience,
Matt