Обсуждение: Function args: TEXT -vs- VARCHAR?

When writing (9.2) PostgreSQL functions, is it preferable to have text
or character varying args?

The tables the functions are updating/inserting into have character
varying columns.

I've used both in various places & want to unify.

Using character varying with an over length arg causes the function to
bomb out on calling, which seems the logical thing to do as the table
can't accept the data anyway.

Thoughts?
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

I like to have constraint, so I usually use character varying.
Anyway, there is no performance difference:
http://www.postgresql.org/docs/9.2/static/datatype-character.html

So use character varying just if you can, or if you must limit the input.

2013/11/12 Craig R. Skinner <skinner@britvault.co.uk>:
> When writing (9.2) PostgreSQL functions, is it preferable to have text
> or character varying args?
>
> The tables the functions are updating/inserting into have character
> varying columns.
>
> I've used both in various places & want to unify.
>
> Using character varying with an over length arg causes the function to
> bomb out on calling, which seems the logical thing to do as the table
> can't accept the data anyway.
>
> Thoughts?
> --
> Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

When writing (9.2) PostgreSQL functions, is it preferable to have text
or character varying args?

The tables the functions are updating/inserting into have character
varying columns.

I've used both in various places & want to unify.

Using character varying with an over length arg causes the function to
bomb out on calling, which seems the logical thing to do as the table
can't accept the data anyway.

Thoughts?

--
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On 2013-11-12 Tue 19:23 PM |, Luca Vernini wrote:
> I like to have constraint, so I usually use character varying.

Same here, I have no text columns. All strings are stored as character
varying.

> Anyway, there is no performance difference:
> http://www.postgresql.org/docs/9.2/static/datatype-character.html
> 

Interesting, although that performance tip is about table storage, not
casting arguments from text to character varying within a function.

> So use character varying just if you can, or if you must limit the input.
> 

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.

Thanks,
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

On 2013-11-12 Tue 19:07 PM |, Sergey Konoplev wrote:
> 
>    There is a good article about
>    this [1]http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-v
>    s-text/.

It is an interesting item about table column types, not function arguments.

Thanks,
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

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

On 2013-11-13 Wed 15:20 PM |, Pavel Stehule wrote:
>    yes, it is fact. Typmod of function arguments is ignored - there are
>    lot of discuss in archive on this topic

Thanks for the update.

An official note of the known limitation in these pages would be appreciated:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/9.2/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

Thanks,
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

On 11/13/2013 06:25 AM, Craig R. Skinner wrote:
> On 2013-11-13 Wed 15:20 PM |, Pavel Stehule wrote:
>>     yes, it is fact. Typmod of function arguments is ignored - there are
>>     lot of discuss in archive on this topic
>
> Thanks for the update.
>
> An official note of the known limitation in these pages would be appreciated:
> http://www.postgresql.org/docs/current/static/sql-createfunction.html
> http://www.postgresql.org/docs/9.2/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html
"
Notes

The full SQL type syntax is allowed for input arguments and return 
value. However, some details of the type specification (e.g., the 
precision field for type numeric) are the responsibility of the 
underlying function implementation and are silently swallowed (i.e., not 
recognized or enforced) by the CREATE FUNCTION command.
"

>
> Thanks,
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

On 2013-11-13 Wed 06:34 AM |, Adrian Klaver wrote:
> 
> http://www.postgresql.org/docs/9.3/interactive/sql-createfunction.html
> "
> Notes
> 
> The full SQL type syntax is allowed for input arguments and return
> value. However, some details of the type specification (e.g., the
> precision field for type numeric) are the responsibility of the
> underlying function implementation and are silently swallowed (i.e.,
> not recognized or enforced) by the CREATE FUNCTION command.
> "
> 

That's rather vague..... "... some details ..."

An explicit table of datatype & what's "silently swallowed" would be
valuable to clarify this topic that's said to be often repeatedly asked.

Cheers,
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

skinner@britvault.co.uk (Craig R. Skinner) writes:
> On 2013-11-13 Wed 06:34 AM |, Adrian Klaver wrote:
>> The full SQL type syntax is allowed for input arguments and return
>> value. However, some details of the type specification (e.g., the
>> precision field for type numeric) are the responsibility of the
>> underlying function implementation and are silently swallowed (i.e.,
>> not recognized or enforced) by the CREATE FUNCTION command.

> That's rather vague..... "... some details ..."

What's ignored by CREATE FUNCTION (and, indeed, pretty much the entire
type inference mechanism) is typmod.  So any parenthesized modifier for
a type name is not considered.  Usually those are length/precision
constraints, but user-defined types might do something else with them.

> An explicit table of datatype & what's "silently swallowed" would be
> valuable to clarify this topic that's said to be often repeatedly asked.

Can't be asked that often, as that text has been untouched for more
than a dozen years ...
        regards, tom lane

Tom Lane-2 wrote
> skinner@.co

>  (Craig R. Skinner) writes:
>> On 2013-11-13 Wed 06:34 AM |, Adrian Klaver wrote:
>>> The full SQL type syntax is allowed for input arguments and return
>>> value. However, some details of the type specification (e.g., the
>>> precision field for type numeric) are the responsibility of the
>>> underlying function implementation and are silently swallowed (i.e.,
>>> not recognized or enforced) by the CREATE FUNCTION command.
> 
>> That's rather vague..... "... some details ..."
> 
> What's ignored by CREATE FUNCTION (and, indeed, pretty much the entire
> type inference mechanism) is typmod.  So any parenthesized modifier for
> a type name is not considered.  Usually those are length/precision
> constraints, but user-defined types might do something else with them.
> 
>> An explicit table of datatype & what's "silently swallowed" would be
>> valuable to clarify this topic that's said to be often repeatedly asked.
> 
> Can't be asked that often, as that text has been untouched for more
> than a dozen years ...

s/some/parenthetical/  ?

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Function-args-TEXT-vs-VARCHAR-tp5777968p5778192.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> Tom Lane-2 wrote
>> skinner@.co (Craig R. Skinner) writes:
>>> An explicit table of datatype & what's "silently swallowed" would be
>>> valuable to clarify this topic that's said to be often repeatedly asked.

>> Can't be asked that often, as that text has been untouched for more
>> than a dozen years ...

> s/some/parenthetical/  ?

[ thinks for a bit ]  Actually, the existing text is wrong/misleading
in another way, since it implies that a PL implementation *could* enforce
parameter typmods if it wanted.  It can't, because the information isn't
even stored by CREATE FUNCTION.  So we do need to do something about this
text.  Perhaps something like this:

The full SQL type syntax is allowed for declaring a function's arguments
and return value.  However, parenthesized type modifiers (e.g., the
precision field for type numeric) are discarded by CREATE FUNCTION.  Thus
for example "CREATE FUNCTION foo (varchar(10)) ..." is exactly the same as
"CREATE FUNCTION foo (varchar) ...".
        regards, tom lane

Tom Lane-2 wrote
> David Johnston <

> polobo@

> > writes:
>> Tom Lane-2 wrote
>>> skinner@.co (Craig R. Skinner) writes:
>>>> An explicit table of datatype & what's "silently swallowed" would be
>>>> valuable to clarify this topic that's said to be often repeatedly
>>>> asked.
> 
>>> Can't be asked that often, as that text has been untouched for more
>>> than a dozen years ...
> 
>> s/some/parenthetical/  ?
> 
> [ thinks for a bit ]  Actually, the existing text is wrong/misleading
> in another way, since it implies that a PL implementation *could* enforce
> parameter typmods if it wanted.  It can't, because the information isn't
> even stored by CREATE FUNCTION.  So we do need to do something about this
> text.  Perhaps something like this:
> 
> The full SQL type syntax is allowed for declaring a function's arguments
> and return value.  However, parenthesized type modifiers (e.g., the
> precision field for type numeric) are discarded by CREATE FUNCTION.  Thus
> for example "CREATE FUNCTION foo (varchar(10)) ..." is exactly the same as
> "CREATE FUNCTION foo (varchar) ...".

+1

FWIW I have always read that as "if you care about stuff like precision and
text lengths you have to deal with it yourself inside your function."  Not
that the PL implementation itself would be capable to doing something with
the information.  Since CREATE FUNCTION was doing the stripping I reasoned
out the actual PL language interpreter never even saw the info.

David J.








--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Function-args-TEXT-vs-VARCHAR-tp5777968p5778200.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On 2013-11-13 Wed 12:51 PM |, Tom Lane wrote:
> 
> The full SQL type syntax is allowed for declaring a function's arguments
> and return value.  However, parenthesized type modifiers (e.g., the
> precision field for type numeric) are discarded by CREATE FUNCTION.  Thus
> for example "CREATE FUNCTION foo (varchar(10)) ..." is exactly the same as
> "CREATE FUNCTION foo (varchar) ...".
> 

That note would be clear to this user.

Thanks,
-- 
Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7

skinner@britvault.co.uk (Craig R. Skinner) writes:
> On 2013-11-13 Wed 12:51 PM |, Tom Lane wrote:
>> The full SQL type syntax is allowed for declaring a function's arguments
>> and return value.  However, parenthesized type modifiers (e.g., the
>> precision field for type numeric) are discarded by CREATE FUNCTION.  Thus
>> for example "CREATE FUNCTION foo (varchar(10)) ..." is exactly the same as
>> "CREATE FUNCTION foo (varchar) ...".

> That note would be clear to this user.

OK, done like that.
        regards, tom lane

On Wed, Nov 13, 2013 at 5:05 AM, Craig R. Skinner
<skinner@britvault.co.uk> wrote:
> On 2013-11-12 Tue 19:07 PM |, Sergey Konoplev wrote:
>>    There is a good article about
>>    this [1]http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-v
>>    s-text/.
>
> It is an interesting item about table column types, not function arguments.

Well, actually if you use doman+constraint it will work with function arguments:

[local]:5432 grayhemp@grayhemp=# create domain text10 text constraint
text10_length_chk check (length(value) <= 10);
CREATE DOMAIN

[local]:5432 grayhemp@grayhemp=# create or replace function f(t
text10) returns void language 'plpgsql' as $$ begin raise info '%', t;
return; end $$;
CREATE FUNCTION

[local]:5432 grayhemp@grayhemp=# select '12345678900'::text10;
ERROR:  value for domain text10 violates check constraint "text10_length"

[local]:5432 grayhemp@grayhemp=# select f('1234567890');
INFO:  1234567890

[local]:5432 grayhemp@grayhemp=# select f('12345678900');
ERROR:  value for domain text10 violates check constraint "text10_length_chk"

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com