BUILD_FLEXTABLE_VIEW

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

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

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

BUILD_FLEXTABLE_VIEW ('[[database.]schema.]flex-table'
    [ [,'view-name'] [,'user-keys-table'] ])

Arguments

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

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
A custom view name. Use this option to build a new view for flex-table with the name you specify.
user-keys-table
Name of a keys table from which to create the view. Use this option if you created a custom 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, rather than from flex_table_keys.

Examples

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

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

  1. Call the function with an input flex table:

    => 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 from the new or updated view:

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

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)

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:

  • 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 * FROMdarkdata_keys;
CREATE TABLE

  • 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)

  • 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)

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)

Query the new view:

=> SELECT * FROM dd_view;

See also