Snowflake: get all context CURRENT_* session functions

Related posts:

• Snowflake: Context Functions
• Oracle: sys_context() getting all user environment variables

At Snowflake get all SQL session parameters:

          select 'CURRENT_ACCOUNT'    , CURRENT_ACCOUNT()
union all select 'CURRENT_CLIENT'     , CURRENT_CLIENT()
union all select 'CURRENT_DATABASE'   , CURRENT_DATABASE()
union all select 'CURRENT_DATE'       , to_char(CURRENT_DATE())
union all select 'CURRENT_ROLE'       , CURRENT_ROLE()
union all select 'CURRENT_SCHEMA'     , CURRENT_SCHEMA()
union all select 'CURRENT_SCHEMAS'    , CURRENT_SCHEMAS()
union all select 'CURRENT_SESSION'    , CURRENT_SESSION()
union all select 'CURRENT_STATEMENT'  , CURRENT_STATEMENT()
union all select 'CURRENT_TIME'       , to_char(CURRENT_TIME())
union all select 'CURRENT_TIMESTAMP'  , to_char(CURRENT_TIMESTAMP())
union all select 'CURRENT_TRANSACTION', CURRENT_TRANSACTION()
union all select 'CURRENT_USER'       , CURRENT_USER()
union all select 'CURRENT_VERSION'    , CURRENT_VERSION()
union all select 'CURRENT_WAREHOUSE'  , CURRENT_WAREHOUSE()
order by 1;

Output:

+---------------------+----------------------------------------------------------------------+
| 'CURRENT_ACCOUNT'   | CURRENT_ACCOUNT()                                                    |
|---------------------+----------------------------------------------------------------------|
| CURRENT_ACCOUNT     | JD12345                                                              |
| CURRENT_CLIENT      | SnowSQL 1.1.82                                                       |
| CURRENT_DATABASE    | GINGER                                                               |
| CURRENT_DATE        | 2019-07-25                                                           |
| CURRENT_ROLE        | SYSADMIN                                                             |
| CURRENT_SCHEMA      | PUBLIC                                                               |
| CURRENT_SCHEMAS     | ["GINGER.PUBLIC"]                                                    |
| CURRENT_SESSION     | 145071110578262                                                      |
| CURRENT_STATEMENT   | select 'CURRENT_ACCOUNT'    , CURRENT_ACCOUNT() ....  order by 1;    |
| CURRENT_TIME        | 16:10:44                                                             |
| CURRENT_TIMESTAMP   | 2019-07-25 16:10:44.990 -0700                                        |
| CURRENT_TRANSACTION | NULL                                                                 |
| CURRENT_USER        | JOHNDOUGH                                                             |
| CURRENT_VERSION     | 3.34.2                                                               |
| CURRENT_WAREHOUSE   | WH_X_SMALL                                                           |
+---------------------+----------------------------------------------------------------------+

Snowflake: the snowsql option OUTPUT_FORMAT to output results delimited and formatted

Refer:

• Snowflake: Using SnowSQL
• Snowflake: snowsql !options

The list of available options output_format= :

snowsql -q 'select...' -o output_format=psql
snowsql -q 'select...' -o output_format=plain
snowsql -q 'select...' -o output_format=simple
snowsql -q 'select...' -o output_format=grid
snowsql -q 'select...' -o output_format=fancy_grid
snowsql -q 'select...' -o output_format=orgtbl
snowsql -q 'select...' -o output_format=rst
snowsql -q 'select...' -o output_format=mediawiki
snowsql -q 'select...' -o output_format=html
snowsql -q 'select...' -o output_format=latex
snowsql -q 'select...' -o output_format=latex_booktabs
snowsql -q 'select...' -o output_format=pipe
snowsql -q 'select...' -o output_format=tsv 
snowsql -q 'select...' -o output_format=csv 

Examples:

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=psql
+---------------+--------------+-------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
|---------------+--------------+-------------|
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=plain
TABLE_CATALOG  TABLE_SCHEMA  TABLE_OWNER
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=simple
TABLE_CATALOG  TABLE_SCHEMA  TABLE_OWNER
-------------  ------------  -----------
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=grid
+---------------+--------------+-------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
+===============+==============+=============+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+
| GINGER        | PUBLIC       | SYSADMIN    |
+---------------+--------------+-------------+

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=fancy_grid
╒═══════════════╤══════════════╤═════════════╕
│ TABLE_CATALOG │ TABLE_SCHEMA │ TABLE_OWNER │
╞═══════════════╪══════════════╪═════════════╡
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
├───────────────┼──────────────┼─────────────┤
│ GINGER        │ PUBLIC       │ SYSADMIN    │
╘═══════════════╧══════════════╧═════════════╛

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=orgtbl
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
|---------------+--------------+-------------|
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=rst
=============  ============  ===========
TABLE_CATALOG  TABLE_SCHEMA  TABLE_OWNER
=============  ============  ===========
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
GINGER         PUBLIC        SYSADMIN
=============  ============  ===========

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=mediawiki
{| class="wikitable" style="text-align: left;"
|+ <!-- caption -->
|-
! TABLE_CATALOG !! TABLE_SCHEMA !! TABLE_OWNER
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|-
| GINGER        || PUBLIC       || SYSADMIN
|}

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=html
<table>
<tr><th>TABLE_CATALOG</th><th>TABLE_SCHEMA</th><th>TABLE_OWNER</th></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
<tr><td>GINGER       </td><td>PUBLIC      </td><td>SYSADMIN   </td></tr>
</table>

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=latex
\begin{tabular}{lll}
\hline
 TABLE\_CATALOG & TABLE\_SCHEMA & TABLE\_OWNER \\
\hline
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
\hline
\end{tabular}

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=latex_booktabs
\begin{tabular}{lll}
\toprule
 TABLE\_CATALOG & TABLE\_SCHEMA & TABLE\_OWNER \\
\midrule
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
 GINGER        & PUBLIC       & SYSADMIN    \\
\bottomrule
\end{tabular}

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=pipe
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_OWNER |
|:--------------|:-------------|:------------|
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |
| GINGER        | PUBLIC       | SYSADMIN    |

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=tsv
TABLE_CATALOG   TABLE_SCHEMA    TABLE_OWNER
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN
GINGER  PUBLIC  SYSADMIN

USER@SERVER$ snowsql -q 'select table_catalog,table_schema,table_owner from information_schema.tables limit 5' -o output_format=csv
"TABLE_CATALOG","TABLE_SCHEMA","TABLE_OWNER"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"
"GINGER","PUBLIC","SYSADMIN"


USER@SERVER$

Snowflake: create OVERLAPS function

Create OVERLAPS (t1,t2,t3,t4) function at Snowflake database

Related links:

• Postgres SQL OVERLAPS
• Intersection of 1D segments
• Snowflake: split string into array, sort it, and return string.

Creating OVERLAPS  UDF function at Snowflake database:

CREATE OR REPLACE FUNCTION  overlaps(V_A_START FLOAT           , V_A_END FLOAT           , V_B_START FLOAT           , V_B_END FLOAT           )
 --create or replace FUNCTION overlaps(V_A_START FLOAT4          , V_A_END FLOAT4          , V_B_START FLOAT4          , V_B_END FLOAT4          )
 --create or replace FUNCTION overlaps(V_A_START FLOAT8          , V_A_END FLOAT8          , V_B_START FLOAT8          , V_B_END FLOAT8          )
 --create or replace FUNCTION overlaps(V_A_START DOUBLE          , V_A_END DOUBLE          , V_B_START DOUBLE          , V_B_END DOUBLE          )
 --create or replace FUNCTION overlaps(V_A_START DOUBLE PRECISION, V_A_END DOUBLE PRECISION, V_B_START DOUBLE PRECISION, V_B_END DOUBLE PRECISION)
 --create or replace FUNCTION overlaps(V_A_START REAL            , V_A_END REAL            , V_B_START REAL            , V_B_END REAL            )
 --create or replace FUNCTION overlaps(V_A_START DATE            , V_A_END DATE            , V_B_START DATE            , V_B_END DATE            )
 --create or replace FUNCTION overlaps(V_A_START DATETIME        , V_A_END DATETIME        , V_B_START DATETIME        , V_B_END DATETIME        )
 --create or replace FUNCTION overlaps(V_A_START TIMESTAMP       , V_A_END TIMESTAMP       , V_B_START TIMESTAMP       , V_B_END TIMESTAMP       )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_LTZ   , V_A_END TIMESTAMP_LTZ   , V_B_START TIMESTAMP_LTZ   , V_B_END TIMESTAMP_LTZ   )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_NTZ   , V_A_END TIMESTAMP_NTZ   , V_B_START TIMESTAMP_NTZ   , V_B_END TIMESTAMP_NTZ   )
 --create or replace FUNCTION goverlaps(V_A_START TIMESTAMP_TZ    , V_A_END TIMESTAMP_TZ    , V_B_START TIMESTAMP_TZ    , V_B_END TIMESTAMP_TZ    )
RETURNS BOOLEAN NOT NULL
LANGUAGE javascript
AS     
$$ 
    RETURN (Math.max(V_A_START,V_A_END) >= Math.min(V_B_START,V_B_END) && Math.max(V_B_START,V_B_END) >= Math.min(V_A_START,V_A_END)) ; 
$$
;

These overloads will NOT work and fail rendering the following error: 

                 Language JAVASCRIPT does not support type 'NUMBER(38,0)' for argument or return type.

-- create or replace FUNCTION overlaps(V_A_START NUMBER          , V_A_END NUMBER          , V_B_START NUMBER          , V_B_END NUMBER          )   
-- create or replace FUNCTION overlaps(V_A_START DECIMAL         , V_A_END DECIMAL         , V_B_START DECIMAL         , V_B_END DECIMAL         )
-- create or replace FUNCTION overlaps(V_A_START NUMERIC         , V_A_END NUMERIC         , V_B_START NUMERIC         , V_B_END NUMERIC         )
-- create or replace FUNCTION overlaps(V_A_START INT             , V_A_END INT             , V_B_START INT             , V_B_END INT             )
-- create or replace FUNCTION overlaps(V_A_START INTEGER         , V_A_END INTEGER         , V_B_START INTEGER         , V_B_END INTEGER         )
-- create or replace FUNCTION overlaps(V_A_START BIGINT          , V_A_END BIGINT          , V_B_START BIGINT          , V_B_END BIGINT          )
-- create or replace FUNCTION overlaps(V_A_START SMALLINT        , V_A_END SMALLINT        , V_B_START SMALLINT        , V_B_END SMALLINT        )
-- create or replace FUNCTION overlaps(V_A_START TINYINT         , V_A_END TINYINT         , V_B_START TINYINT         , V_B_END TINYINT         )
-- create or replace FUNCTION overlaps(V_A_START BYTEINT         , V_A_END BYTEINT         , V_B_START BYTEINT         , V_B_END BYTEINT         )

Verify the working logic:

SELECT overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-02 00:00:00') AS OVERLAPS_LEFT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS OVERLAPS_RIGHT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:01', TIMESTAMP'2019-01-02 23:59:59') AS OVERLAPS_INISDE
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS OVERLAPS_OUTSIDE
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-01 23:59:59') AS NOT_OVERLAPS_LEFT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-03 00:00:01', TIMESTAMP'2019-01-04 00:00:00') AS NOT_OVERLAPS_RIGHT
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',NULL, NULL) AS NOT_OVERLAPS_NULLS
      ,overlaps( NULL, TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-04 00:00:00') AS NOT_OVERLAPS_NULLS
      ,overlaps(TIMESTAMP'2019-01-03 00:00:00', TIMESTAMP'2019-01-02 00:00:00' ,TIMESTAMP'2019-01-01 00:00:00', TIMESTAMP'2019-01-02 00:00:00') AS OVERLAPS_reverseda
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00',TIMESTAMP'2019-01-01 00:00:00'  ) AS OVERLAPS_reversedb
      ,overlaps(TIMESTAMP'2019-01-02 00:00:00', TIMESTAMP'2019-01-03 00:00:00',TIMESTAMP'2019-01-02 00:00:00',TIMESTAMP'2019-01-01 00:00:00'  ) AS OVERLAPS_reversedboth
;

Finally, check the all existent overloads of the function:

show functions like 'overlaps';