Re: Smallint needs explicit cast in psql?
От | Matt Musgrove |
---|---|
Тема | Re: Smallint needs explicit cast in psql? |
Дата | |
Msg-id | C0FE28B9352B6F4F8CE371643EC3B25121083555@EFJDFWMB01.EFJDFW.local обсуждение исходный текст |
Ответ на | Re: Smallint needs explicit cast in psql? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Smallint needs explicit cast in psql?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: