Re: Function args: TEXT -vs- VARCHAR?

Hello

2013/11/13 Craig R. Skinner <skinner(at)britvault(dot)co(dot)uk>

> 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.
>

yes, it is fact. Typmod of function arguments is ignored - there are lot of
discuss in archive on this topic

Regards

Pavel

>
> 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$
>
> BEGIN
> INSERT 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>