agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

Function args: TEXT -vs- VARCHAR?
17+ messages / 7 participants
[nested] [flat]

* Function args: TEXT -vs- VARCHAR?
@ 2013-11-12 18:17  Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 2 replies; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-12 18:17 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-12 18:23  Luca Vernini <lucazeo@gmail.com>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  1 sibling, 1 reply; 17+ messages in thread

From: Luca Vernini @ 2013-11-12 18:23 UTC (permalink / raw)
  To: Craig R. Skinner <skinner@britvault.co.uk>; +Cc: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 03:07  Sergey Konoplev <gray.ru@gmail.com>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  1 sibling, 1 reply; 17+ messages in thread

From: Sergey Konoplev @ 2013-11-13 03:07 UTC (permalink / raw)
  To: Craig R. Skinner <skinner@britvault.co.uk>; +Cc: pgsql-sql

On Tuesday, November 12, 2013, Craig R. Skinner wrote:

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


There is a good article about this
http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/.


> --
> Craig Skinner | http://twitter.com/Craig_Skinner | http://linkd.in/yGqkv7
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org <javascript:;>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


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

^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 13:03  Craig R. Skinner <skinner@britvault.co.uk>
  parent: Luca Vernini <lucazeo@gmail.com>
  0 siblings, 1 reply; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-13 13:03 UTC (permalink / raw)
  To: 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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 13:05  Craig R. Skinner <skinner@britvault.co.uk>
  parent: Sergey Konoplev <gray.ru@gmail.com>
  0 siblings, 1 reply; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-13 13:05 UTC (permalink / raw)
  To: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 14:17  Craig R. Skinner <skinner@britvault.co.uk>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 1 reply; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-13 14:17 UTC (permalink / raw)
  To: pgsql-sql

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$

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 14:20  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 1 reply; 17+ messages in thread

From: Pavel Stehule @ 2013-11-13 14:20 UTC (permalink / raw)
  To: Craig R. Skinner <skinner@britvault.co.uk>; +Cc: pgsql-sql

Hello


2013/11/13 Craig R. Skinner <skinner@britvault.co.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 14:25  Craig R. Skinner <skinner@britvault.co.uk>
  parent: Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 1 reply; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-13 14:25 UTC (permalink / raw)
  To: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 14:34  Adrian Klaver <adrian.klaver@gmail.com>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 1 reply; 17+ messages in thread

From: Adrian Klaver @ 2013-11-13 14:34 UTC (permalink / raw)
  To: Craig R. Skinner <skinner@britvault.co.uk>; pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 15:51  Craig R. Skinner <skinner@britvault.co.uk>
  parent: Adrian Klaver <adrian.klaver@gmail.com>
  0 siblings, 1 reply; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-13 15:51 UTC (permalink / raw)
  To: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 16:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 1 reply; 17+ messages in thread

From: Tom Lane @ 2013-11-13 16:36 UTC (permalink / raw)
  To: skinner@britvault.co.uk; +Cc: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 17:14  David Johnston <polobo@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 17+ messages in thread

From: David Johnston @ 2013-11-13 17:14 UTC (permalink / raw)
  To: pgsql-sql

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.


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 17:51  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David Johnston <polobo@yahoo.com>
  0 siblings, 2 replies; 17+ messages in thread

From: Tom Lane @ 2013-11-13 17:51 UTC (permalink / raw)
  To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 17:59  David Johnston <polobo@yahoo.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 17+ messages in thread

From: David Johnston @ 2013-11-13 17:59 UTC (permalink / raw)
  To: pgsql-sql

Tom Lane-2 wrote
> David Johnston &lt;

> polobo@

> &gt; 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.


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 18:04  Craig R. Skinner <skinner@britvault.co.uk>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 17+ messages in thread

From: skinner@britvault.co.uk @ 2013-11-13 18:04 UTC (permalink / raw)
  To: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-13 18:31  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 0 replies; 17+ messages in thread

From: Tom Lane @ 2013-11-13 18:31 UTC (permalink / raw)
  To: skinner@britvault.co.uk; +Cc: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

* Re: Function args: TEXT -vs- VARCHAR?
@ 2013-11-14 07:41  Sergey Konoplev <gray.ru@gmail.com>
  parent: Craig R. Skinner <skinner@britvault.co.uk>
  0 siblings, 0 replies; 17+ messages in thread

From: Sergey Konoplev @ 2013-11-14 07:41 UTC (permalink / raw)
  To: Craig R. Skinner <skinner@britvault.co.uk>; +Cc: pgsql-sql

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


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



^ permalink  raw  reply  [nested|flat] 17+ messages in thread

end of thread, other threads:[~2013-11-14 07:41 UTC | newest]

Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-12 18:17 Function args: TEXT -vs- VARCHAR? Craig R. Skinner <skinner@britvault.co.uk>
2013-11-12 18:23 ` Luca Vernini <lucazeo@gmail.com>
2013-11-13 13:03   ` Craig R. Skinner <skinner@britvault.co.uk>
2013-11-13 14:17     ` Craig R. Skinner <skinner@britvault.co.uk>
2013-11-13 14:20       ` Pavel Stehule <pavel.stehule@gmail.com>
2013-11-13 14:25         ` Craig R. Skinner <skinner@britvault.co.uk>
2013-11-13 14:34           ` Adrian Klaver <adrian.klaver@gmail.com>
2013-11-13 15:51             ` Craig R. Skinner <skinner@britvault.co.uk>
2013-11-13 16:36               ` Tom Lane <tgl@sss.pgh.pa.us>
2013-11-13 17:14                 ` David Johnston <polobo@yahoo.com>
2013-11-13 17:51                   ` Tom Lane <tgl@sss.pgh.pa.us>
2013-11-13 17:59                     ` David Johnston <polobo@yahoo.com>
2013-11-13 18:04                     ` Craig R. Skinner <skinner@britvault.co.uk>
2013-11-13 18:31                       ` Tom Lane <tgl@sss.pgh.pa.us>
2013-11-13 03:07 ` Sergey Konoplev <gray.ru@gmail.com>
2013-11-13 13:05   ` Craig R. Skinner <skinner@britvault.co.uk>
2013-11-14 07:41     ` Sergey Konoplev <gray.ru@gmail.com>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox