[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread jam3
Yeah thats what I was starting to wonder if those lengths basically mean
nothing. I am writing a ton of functions to unit test all of the functions
in our app and am generating random strings and would like to pass the
lengths to my random string generator so if it's varchar 50 I am generating
a string between 0 and 50 length but since I can't find the length value I
guess I am just going to put an arbitrary length in. 

Would be nice to know what exactly is going on when you have a length
specified on an input variable in pg_catalog.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722850.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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


[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread Kevin Grittner
jam3 jamort...@gmail.com wrote:
 
 create or replace function test1(c1 char(10), c2 varchar(20))
 
 Just showing that it does indeed not use the length in at all
 
Correct.  That is functioning as intended and is not likely to
change any time soon.
 
You might consider using domains:
 
drop function if exists test1(c1 t1, c2 t2);
drop table if exists test_table;
drop domain if exists t1;
drop domain if exists t2;

create domain t1 varchar(10);
create domain t2 varchar(20);
create table test_table
(
  column1 char(20),
  column2 varchar(40)
) without oids;
create or replace function test1(c1 t1, c2 t2)
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql;
select
test1('12345678900123456789',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD');
select * from test_table;
 
-Kevin


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


Re: [GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread David Johnston
 -Original Message-
 From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
 ow...@postgresql.org] On Behalf Of jam3
 Sent: Wednesday, September 05, 2012 3:34 PM
 To: pgsql-general@postgresql.org
 Subject: [GENERAL] Re: Where is the char and varchar length in pg_catalog
 for function input variables
 
 This is what I meant to post
 
 ..
 
 Just showing that it does indeed not use the length in at all, and this
just
 seems wrong. I can definetly see situations where someone would put a
 length on a in put var and get an an unexpected result, like the one
above.
 

You can argue it is wrong, and I'd tend to agree.  But that is how things
are until someone decides it is painful enough to implement a better way.

It is a documented situation though suggestions for improvements there are
always welcome.

If/when you care you can implement adhoc validation inside the function.

Discoverability via meta-data is the nice but lacking ability with the
current model but for arbitrary length and precision/scale specifications
that ability has limited (but non-zero) value. For better and worse you can
extend the system tables and include the meta-data that you feel is
necessary to make the system work.  It is a much less invasive procedure
than altering the catalogs themselves.

David J.






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


[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread jam3
Duh never mind I call brain cloud on that one, and thanks for all the help.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722880.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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


[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread jam3
This is what I meant to post

drop table test_table;
create table test_table
(
column1 char(20),
column2 varchar(40)
) without oids;


drop function test1(char(10), varchar(20)); 
create or replace function test1(c1 char(10), c2 varchar(20))
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql 

select
test1('12345678900123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD')

select * from test_table;
12345678900123456789, ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD


Just showing that it does indeed not use the length in at all, and this just
seems wrong. I can definetly see situations where someone would put a length
on a in put var and get an an unexpected result, like the one above.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722881.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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


[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread jam3
How does postgres figure this out to throw the error msg?

create table test_table
(
column1 char(10),
column2 varchar(20)
) without oids;


create or replace function test1(c1 char(10), c2 varchar(20))
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql 

select test1('1234567890','ABCDEFGHIJKLMNOPQRST')

select * from test_table;
-- 1234567890, ABCDEFGHIJKLMNOPQRST

select test1('this is way way longer than 10 characters','this is way way
way way way way way way way way way way longer than 20 characters')

ERROR:  value too long for type character(10)
CONTEXT:  SQL statement insert into test_table values ($1, $2)
PL/pgSQL function test1 line 3 at SQL statement

** Error **

ERROR: value too long for type character(10)



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722876.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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


Re: [GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread David Johnston
 
 How does postgres figure this out to throw the error msg?
 
 
 select test1('this is way way longer than 10 characters','this is way way
way
 way way way way way way way way way longer than 20 characters')
 
 ERROR:  value too long for type character(10)
 CONTEXT:  SQL statement insert into test_table values ($1, $2)
 PL/pgSQL function test1 line 3 at SQL statement
 
 ** Error **
 
 ERROR: value too long for type character(10)
 

When it goes to execute:

INSERT INTO test_table ('this is way way ...', 'this is way way way...')

The char(10) type definition for test_table.column1 is too short to hold the
supplied value (stored in $1 in the function) and throws an error.

The length of $1 and $2 inside the function are however long the input
values are because they ignore the length specifier on the function call
types.

If you want to guarantee that the INSERT will work you would need to write:

INSERT INTO test_table VALUES ( $1::char(10), $2::varchar(20) )

This tells PostgreSQL to truncate the supplied value at whatever specified
length is noted; the same as writing substring($1, 1, 10)::char or
substring($1, 1, 20)::varchar though whether char and varchar differ in
their behavior in this respect I do not know.  It is generally not
recommended to use char

David J.







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


Re: [GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables

2012-09-05 Thread Kevin Grittner
David Johnston pol...@yahoo.com wrote:
 
 If you want to guarantee that the INSERT will work you would need
 to write:
 
 INSERT INTO test_table VALUES ( $1::char(10), $2::varchar(20) )
 
Note that this will quietly cut off the tail end of the supplied
data, so it should only be used when that is desirable.  It is
generally better to throw an error than to lose data.
 
 whether char and varchar differ in their behavior in this
 respect I do not know.
 
Per the SQL standard, they both throw an error on attempts to assign
an oversized value, but allow truncation through explicit casts.
 
 It is generally not recommended to use char
 
Right.  It is supported because the standard specifies it and its
behavior, but the semantics of char(n) are weird and the
performance, in PostgreSQL, is generally worse for char(n) than
varchar(n) or text.
 
-Kevin


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