The Vertica Database Forums

Code: Select all

dbadmin=> create flex table test_flex();
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/test.txt
1,"test",test1
"test3",2

dbadmin=> copy test_flex from '/home/dbadmin/test.txt' parser fdelimitedparser(header=false,delimiter=',');
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select compute_flextable_keys_and_build_view('test_flex');
                                 compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------------
 Please see public.test_flex_keys for updated keys
The view public.test_flex_view is ready for querying
(1 row)

dbadmin=> select * from public.test_flex_view;
  ucol0  | ucol1  | ucol2
---------+--------+-------
 1       | "test" | test1
 "test3" | 2      |
(2 rows)

Code: Select all

dbadmin=> CREATE OR REPLACE VIEW public.test_flex_view AS
dbadmin->  SELECT TRIM(BOTH '"' FROM (MapLookup(test_flex.__raw__, 'ucol0'::varchar(5)))::varchar(20)) AS ucol0,
dbadmin->         TRIM(BOTH '"' FROM (MapLookup(test_flex.__raw__, 'ucol1'::varchar(5)))::varchar(20)) AS ucol1,
dbadmin->         TRIM(BOTH '"' FROM (MapLookup(test_flex.__raw__, 'ucol2'::varchar(5)))::varchar(20)) AS ucol2
dbadmin->  FROM public.test_flex;
CREATE VIEW

dbadmin=> SELECT * FROM public.test_flex_view;
 ucol0 | ucol1 | ucol2
-------+-------+-------
 1     | test  | test1
 test3 | 2     |
(2 rows)