Re: lowercase on columnname using view

On 8 May 2012 13:00, Chrishelring <christianhelring(at)gmail(dot)com> wrote:

> Hi all,
>
> had some help the other day, but now I´m kinda stuck again. :/
>
> I have a table ("virksomhedsdata") with the following columns:
>
> "MI_STYLE" character varying(254),
> "MI_PRINX" integer NOT NULL DEFAULT
> nextval('rk_ois."virksomhedsdata_MI_PRINX_seq"'::regclass),
> "SP_GEOMETRY" geometry,
>
> I would like to make a view so that the columnnames are presented in
> lowercase. I thought that the following would work:
>
> CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
> SELECT virksomhedsdata.MI_STYLE AS mi_style, virksomhedsdata.MI_PRINX as
> mi_prinx, virksomhedsdata.SP_GEOMETRY AS sp_geometry
> FROM rk_ois.virksomhedsdata;
>
> But it fails saying that column virksomhedsdata.mi_style does not exist.
>
> What am I doing wrong here?
>
> thanks!
>
> Christian
>
>
If you created the columns like "MI_PRINX", then you need to call them
using "MI_PRINX", not MI_PRINX, because it will be change to lowercase in
the query.

So the proper query should look like this:

CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
SELECT
virksomhedsdata."MI_STYLE" AS mi_style,
virksomhedsdata."MI_PRINX" as mi_prinx,
virksomhedsdata."SP_GEOMETRY" AS sp_geometry
FROM rk_ois.virksomhedsdata;

- szymon