Redefining a column within a view

Jeffrey Schade wrote:

We have a table which contains a 3 byte column with datatype CHAR(3) which we want to redefine within
the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR function the resulting
column datatype is TEXT. I would like to see the CHAR datatype, is there anything I can do to set the
proper datatype. The sample view is below:

CREATE OR REPLACE VIEW schema.jeff_view AS
SELECT col1 ,
col2,
Substr(col3,1,1) as col3_part1,
Substr(col3,2) as col3_part2,
col4
FROM schema.jeff_table;

Try:
[...]
Substr(col3,1,1)::char(1) as col3_part1,
Substr(col3,2)::char(2) as col3_part2,
[...]

Yours,
Laurenz Albe

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

Good Morning Laurenz,

That worked perfectly.

Thank You

Jeff
_________________________________________
Jeffrey Schade
Systems Consultant, Technology Engineering

ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738
  FAX: (201) 748-1500  
jschade@iso.com

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Friday, February 22, 2013 8:31 AM
To: Schade, Jeffrey; pgsql-general@postgresql.org
Subject: RE: Redefining a column within a view

Jeffrey Schade wrote:

We have a table which contains a 3 byte column with datatype CHAR(3)
which we want to redefine within the view as a CHAR(1) column and a
CHAR(2) column. When I code the SUBSTR function the resulting column
datatype is TEXT. I would like to see the CHAR datatype, is there anything I can do to set the proper datatype. The sample view is below:

CREATE OR REPLACE VIEW schema.jeff_view AS SELECT col1 ,
col2,
Substr(col3,1,1) as col3_part1,
Substr(col3,2) as col3_part2,
col4
FROM schema.jeff_table;

Try:
[...]
Substr(col3,1,1)::char(1) as col3_part1,
Substr(col3,2)::char(2) as col3_part2,
[...]

Yours,
Laurenz Albe

This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose.

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