Skip to main content
Question

Flex table virtual column using a substring

  • May 30, 2023
  • 2 replies
  • 9 views
bmurrell
Forum|alt.badge.img+2

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 ?

    2 replies

    Ariel_Cary
    Forum|alt.badge.img
    • Ariel_Cary
    • Participating Frequently
    • May 30, 2023

    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
    Forum|alt.badge.img+2
    • bmurrell
    • Author
    • Participating Frequently
    • May 30, 2023

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement