Обсуждение: Smallint needs explicit cast in psql?
Does psql require smallints to be explicitly cast as such? If so then where is this documented? If not then what am I doing wrong? J
Given the simplest test case I could think of:
CREATE OR REPLACE FUNCTION test_issue(
parm smallint
) RETURNS VOID AS $$
BEGIN
RETURN;
END;
$$ LANGUAGE plpgsql;
nms=# \df test_issue
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------------+------------------+---------------------+--------
public | test_issue | void | parm smallint | normal
(1 row)
nms=# select test_issue(1);
ERROR: function test_issue(integer) does not exist
LINE 1: select test_issue(1);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Matthew Musgrove
EF Johnson Technologies, Inc.
Senior Design Engineer
O 972.819.2357
F 972.819.0639
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