Flex table virtual column using a substring

bmurrell

I'm loading into a flex table which contains a "Path.Path" flattened item that contains the full path of a file.
I'd like a virtual column that is just the top level directory, so was trying the following : -

=> copy sometable (__raw__, "directory" as substring("Path.Path",1,instr("Path.Path",'/',1,2))) FROM 'somefile' parser fjsonparser();
ERROR 2645:  Column directory has other computed columns in its expression
HINT:  Expressions can only contain columns loaded from input

How can I achieve this ?

Ariel_Cary

Value of "Path.Path" isn't available at load time. Flattening has to happen first. That is done by the parser FJSONParser.

You can accomplish you goal in two steps. First you load the path. Then you compute the path prefix with the loaded path.
Something like this:

create flex table f1(();

copy f1 from stdin parser fjsonparser();
{"Path" : {"Path" : "/a/b"} }
\.

-- Get view definition to customize it with prefix column
select compute_flextable_keys_and_build_view('f1');
select view_definition from views where table_name = 'f1_view';
                   view_definition                   
-----------------------------------------------------
 SELECT "path.path"::!"Varchar"(20)  FROM public.f1;
(1 row

-- create another with with path prefix
create view f1_path_prefix_view
AS SELECT "Path.Path"::!"Varchar"(20), substring("Path.Path"::!"Varchar"(20), 1, instr("Path.Path",'/',1,2)) as path_prefix FROM public.f1;

select * from f1_path_prefix_view;
 path.path | path_prefix 
-----------+-------------
 /a/b      | /a/
(1 row)

bmurrell

I'd like to build a projection with the path_prefix for filtering. I tried using a where part "where "Path.Path" like '/%' " on the main table but get an error "Operator does not exist: long varchar ~~ unknown".
I think I might use the flex table as a staging area and then load into a regular main table. Then I can do what I want.
Thanks for you help.