On 2013-11-13 Wed 13:03 PM |, Craig R. Skinner wrote:
>
> Yes, I'll change the function args to be the same as the table columns
> so the functions fail on over length input, rather than going through
> the process of validating customer id & account, only to fail on data.
>
> Therefore => performace increase with character varying function args.
>
Well, I got a SURPRISE there as it seems PostgreSQL function arguments
loose their precision.
Character varying(n) args become character varying / text.
I would have thought that this function should fail when called, not at
the INSERT phase:
--=======
CREATE TABLE rubbish
(junk character varying(12) NOT NULL
);
--=======
CREATE OR REPLACE FUNCTION load_rubbish(gash character varying(12))
RETURNS boolean AS
$BODY$
BEGININSERT INTO rubbish( junk)VALUES( gash);
RETURN FOUND;
END;
$BODY$ LANGUAGE plpgsql;
--=======
SELECT * FROM load_rubbish('Waaaay toooo loooong!');
--=======
********** Error **********
ERROR: value too long for type character varying(12)
SQL state: 22001
Context: SQL statement "INSERT INTO rubbish( junk)VALUES( gash)"
PL/pgSQL function load_rubbish(character varying) line 4 at SQL statement
^^^ The length limit has gone.
This page does not say the precision is stripped:
"... Functions written in PL/pgSQL can accept as arguments any scalar or
array data type supported by the server, ...."
http://www.postgresql.org/docs/9.2/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS
Neither does this page:
argtype".... The argument types can be base, ...."
http://www.postgresql.org/docs/current/static/sql-createfunction.html
Comments?
--
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7
