Re: [GENERAL] Automatic truncation of character values casting to the type of a column type

2009-12-17 Thread Justin Bailey
On Wed, Dec 16, 2009 at 7:20 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Really?  Works for me, in everything back to 7.3.


I must be missing something, because this function fails:

  CREATE OR REPLACE FUNCTION insertShort()
  RETURNS VOID AS
  $BODY$
  DECLARE
s Short.shortCol%TYPE;
  BEGIN
SELECT longCol INTO s FROM Long
WHERE char_length(longCol)  20;
 INSERT INTO Short (shortCol) VALUES (s);
  END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

If s was automatically truncated, the insert would succeed, but it
fails with a value too long error.

p.s. I used the definitions from my prior email:

 CREATE TABLE Long (longCol varchar(40) );
 CREATE TABLE Short (shortCol varchar(20) );

INSERT INTO Long VALUES ('FOOBAR'), ('BAZ'),
(CAST('2314J1L234J21LK342JKL32J32KL4J123LK4J13L4' AS VARCHAR(40)));

-- 
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] Automatic truncation of character values casting to the type of a column type

2009-12-17 Thread Tom Lane
Justin Bailey jgbai...@gmail.com writes:
 On Wed, Dec 16, 2009 at 7:20 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Really?  Works for me, in everything back to 7.3.

 If s was automatically truncated, the insert would succeed, but it
 fails with a value too long error.

Oh, I thought the failure was the behavior you wanted.  There's
no automatic truncation here --- perhaps putting an exception
block around it would be the way to go?

regards, tom lane

-- 
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] Automatic truncation of character values casting to the type of a column type

2009-12-17 Thread Sam Mason
On Thu, Dec 17, 2009 at 10:24:28AM -0500, Tom Lane wrote:
 Justin Bailey jgbai...@gmail.com writes:
  If s was automatically truncated, the insert would succeed, but it
  fails with a value too long error.
 
 Oh, I thought the failure was the behavior you wanted.  There's
 no automatic truncation here --- perhaps putting an exception
 block around it would be the way to go?

I think the OP wants to be able to use the %TYPE magic in more places
than the grammar currently accepts.  For example, so that you can use it
in a CAST expression as per some of the examples.

Would using DOMAINs be a better alternative?  It certainly works now.

-- 
  Sam  http://samason.me.uk/

-- 
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] Automatic truncation of character values casting to the type of a column type

2009-12-17 Thread Adrian Klaver
On Wednesday 16 December 2009 5:05:19 pm Justin Bailey wrote:
 Greetings! I am trying to avoid the old problem of inserting a 40
 character string into a 20 character field. However, I'd like to avoid
 hard-coding the acceptable length (20). Is there a way to say cast to
 the same type as a given column? E.g., if I have tables Long and
 Short:

  CREATE TABLE Long (longCol varchar(40) )
  CREATE TABLE Short (shortCol varchar(20) )

 And this data:

   INSERT INTO Long VALUES ('FOOBAR'), ('BAZ'),
 (CAST('2314J1L234J21LK342JKL32J32KL4J123LK4J13L4' AS VARCHAR(40)))

 Can make values inserted into shortCol have a maximum length of 20
 without hard-coding that value? Something like:

  INSERT INTO Short (ShortCol)
        (SELECT CAST(Long.longCol as Short.shortCol) FROM LONG)

 I am using postgres 8.2.

 Clearly this is a toy example. In the real world, I insert or update
 values in my target table using a stored procedure. I want to
 future-proof my stored procedure against the column lengths on the
 target table changing. Otherwise, I have to update my sproc with new
 lengths if the table ever changes. I have tried using the PL/PGSQL
 feature where types can be copied in a declaration:

  DECLARE
     myVal Short.shortCol%TYPE;
  ...

 But I can still put values which are too long into that variable, so
 it doesn't help me. Sadly, using the same syntax in a CAST fails in
 various ways:

  UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol) -- schema
 Short does not exist error
  UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol%TYPE) -- syntax
 error UPDATE Short SET shortCol = CAST(myVal AS (Short).shortCol) -- syntax
 error

 Thanks in advance for any advice.

 Justin

My solution would be to declare the varchar without a length restriction and 
not 
worry. Right off the top I see two potential problems with the truncation 
procedure you are proposing. One, is if you go and reduce the field width for 
the table column you will have the same truncate error. Two, what happens to 
the 20 characters you are losing? They where important once are they not now?

-- 
Adrian Klaver
akla...@comcast.net

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


[GENERAL] Automatic truncation of character values casting to the type of a column type

2009-12-16 Thread Justin Bailey
Greetings! I am trying to avoid the old problem of inserting a 40
character string into a 20 character field. However, I'd like to avoid
hard-coding the acceptable length (20). Is there a way to say cast to
the same type as a given column? E.g., if I have tables Long and
Short:

  CREATE TABLE Long (longCol varchar(40) )
  CREATE TABLE Short (shortCol varchar(20) )

And this data:

   INSERT INTO Long VALUES ('FOOBAR'), ('BAZ'),
(CAST('2314J1L234J21LK342JKL32J32KL4J123LK4J13L4' AS VARCHAR(40)))

Can make values inserted into shortCol have a maximum length of 20
without hard-coding that value? Something like:

  INSERT INTO Short (ShortCol)
(SELECT CAST(Long.longCol as Short.shortCol) FROM LONG)

I am using postgres 8.2.

Clearly this is a toy example. In the real world, I insert or update
values in my target table using a stored procedure. I want to
future-proof my stored procedure against the column lengths on the
target table changing. Otherwise, I have to update my sproc with new
lengths if the table ever changes. I have tried using the PL/PGSQL
feature where types can be copied in a declaration:

  DECLARE
 myVal Short.shortCol%TYPE;
  ...

But I can still put values which are too long into that variable, so
it doesn't help me. Sadly, using the same syntax in a CAST fails in
various ways:

  UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol) -- schema
Short does not exist error
  UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol%TYPE) -- syntax error
  UPDATE Short SET shortCol = CAST(myVal AS (Short).shortCol) -- syntax error

Thanks in advance for any advice

Justin

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


[GENERAL] Automatic truncation of character values casting to the type of a column type

2009-12-16 Thread Justin Bailey
Greetings! I am trying to avoid the old problem of inserting a 40
character string into a 20 character field. However, I'd like to avoid
hard-coding the acceptable length (20). Is there a way to say cast to
the same type as a given column? E.g., if I have tables Long and
Short:

 CREATE TABLE Long (longCol varchar(40) )
 CREATE TABLE Short (shortCol varchar(20) )

And this data:

  INSERT INTO Long VALUES ('FOOBAR'), ('BAZ'),
(CAST('2314J1L234J21LK342JKL32J32KL4J123LK4J13L4' AS VARCHAR(40)))

Can make values inserted into shortCol have a maximum length of 20
without hard-coding that value? Something like:

 INSERT INTO Short (ShortCol)
       (SELECT CAST(Long.longCol as Short.shortCol) FROM LONG)

I am using postgres 8.2.

Clearly this is a toy example. In the real world, I insert or update
values in my target table using a stored procedure. I want to
future-proof my stored procedure against the column lengths on the
target table changing. Otherwise, I have to update my sproc with new
lengths if the table ever changes. I have tried using the PL/PGSQL
feature where types can be copied in a declaration:

 DECLARE
    myVal Short.shortCol%TYPE;
 ...

But I can still put values which are too long into that variable, so
it doesn't help me. Sadly, using the same syntax in a CAST fails in
various ways:

 UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol) -- schema
Short does not exist error
 UPDATE Short SET shortCol = CAST(myVal AS Short.shortCol%TYPE) -- syntax error
 UPDATE Short SET shortCol = CAST(myVal AS (Short).shortCol) -- syntax error

Thanks in advance for any advice.

Justin

-- 
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] Automatic truncation of character values casting to the type of a column type

2009-12-16 Thread Tom Lane
Justin Bailey jgbai...@gmail.com writes:
 I have tried using the PL/PGSQL
 feature where types can be copied in a declaration:

   DECLARE
  myVal Short.shortCol%TYPE;

 But I can still put values which are too long into that variable, so
 it doesn't help me.

Really?  Works for me, in everything back to 7.3.

regards, tom lane

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