BUILD_FLEXTABLE_VIEW

Creates, or re-creates, a view for a default or user-defined _keys table, ignoring any empty keys.

Syntax

build_flextable_view('flex_table' [ [,'view_name'] [,'user_keys_table'] ])

Arguments

flex_table

The flex table name. By default, this function builds or rebuilds a view for the input table with the current contents of the associated flex_table_keys table.

view_name

[Optional] A custom view name. Use this option to build a new view for flex_table with the name you specify.

user_keys_table

[Optional] Specifies a keys table from which to create the view. Use this option if you created a custom user_keys table from the flex table map data, rather than from the default flex_table_keys table. The function builds a view from the keys in user_keys table, rather than from the flex_table_keys table.

Examples

The following examples show how to call build_flextable_view with 1, 2, or 3 arguments.

Creating a Default View

To create, or re-create, a default view:

  1. Call the function with an input flex table, darkdata: 

    => SELECT build_flextable_view('darkdata');
                  build_flextable_view
-----------------------------------------------------
 The view public.darkdata_view is ready for querying
(1 row)

    The function creates a view with the default name (darkdata_view) from the darkdata_keys table.

  2. Query a key name (user.id) from the new or updated view:

    => SELECT "user.id" from darkdata_view;
  user.id
-----------
 340857907
 727774963
 390498773
 288187825
 164464905
 125434448
 601328899
 352494946
(12 rows)

Creating a Custom Name View

To create, or re-create, a view with a custom name:

  1. Call the function with two arguments, an input flex table, darkdata, and the name of the view to create, dd_view: 

    => SELECT build_flextable_view('darkdata', 'dd_view');
            build_flextable_view
-----------------------------------------------
 The view public.dd_view is ready for querying
(1 row)

  2. Query a key name (user.lang) from the new or updated view (dd_view):

    => SELECT "user.lang" from dd_view;
 user.lang
-----------
 tr
 en
 es
 en
 en
 it
 es
 en
(12 rows)

Creating a View from a Custom Keys Table

To create a view from a custom _keys table with build_flextable_view, the custom table must have the same schema and table definition as the default table (darkdata_keys). Create a custom keys table, using any of these three approaches:

  1. Create a columnar table with all keys from the default keys table for a flex table (darkdata_keys): 

    => CREATE table new_darkdata_keys as select * from darkdata_keys;
CREATE TABLE

  2. Create a columnar table without content (LIMIT 0) from the default keys table for a flex table (darkdata_keys):

    => CREATE table new_darkdata_keys as select * from darkdata_keys LIMIT 0;
CREATE TABLE
kdb=> select * from new_darkdata_keys;
 key_name | frequency | data_type_guess
----------+-----------+-----------------
(0 rows)

  3. Create a columnar table without content (LIMIT 0) from the default keys table, and insert two values ('user.lang', 'user.name') into the key_name column: 

    => CREATE table dd_keys as select * from darkdata_keys limit 0;
CREATE TABLE
=> insert into dd_keys (key_name) values ('user.lang');
 OUTPUT
--------
      1
(1 row)
=> INSERT into dd_keys (key_name) values ('user.name');
 OUTPUT
--------
      1
(1 row)
=> SELECT * from dd_keys;
 key_name  | frequency | data_type_guess
-----------+-----------+-----------------
 user.lang |           |
 user.name |           |
(2 rows)
  4. After creating a custom keys table, call build_flextable_view with all arguments (an input flex table, the new view name, the custom keys table): 
    => SELECT BUILD_FLEXTABLE_VIEW('darkdata', 'dd_view', 'dd_keys');
            build_flextable_view
-----------------------------------------------
 The view public.dd_view is ready for querying
(1 row)

  5. Query the new view: 

    => SELECT * from dd_view;

See Also