How to generate Stackoverflow table markdown from Snowflake

The following stored procedure accepts either a query string or a query ID (it will auto-detect which it is) and returns the table results as Stackoverflow table markdown. It will automatically align numbers and dates to the right, strings, arrays, and objects to the left, and other types default to centered. It supports any query you can pass to it. It may be a good idea to use $$ to terminate the string passed into the procedure in case the SQL contains single quotes. You can create the procedure and test it using this script:

create or replace procedure MARKDOWN("queryOrQueryId" string)
returns string
language javascript
execute as caller
as
$$
    const MAX_ROWS = 50;  // Set the maximum row count to fetch. Tables in markdown larger than this become hard to read.
    
    var [rs, i, c, row, props] = [null, 0, 0, 0, {}];  
    if (!queryOrQueryId || queryOrQueryId == 0){
        queryOrQueryId = `select * from table(result_scan(last_query_id())) limit ${MAX_ROWS}`;
    }
    queryOrQueryId = queryOrQueryId.trim();
    if (isUUID(queryOrQueryId)){
        rs = snowflake.execute({sqlText:`select * from table(result_scan('${queryOrQueryId}')) limit ${MAX_ROWS}`});
    } else {
        rs = snowflake.execute({sqlText:`${queryOrQueryId}`});
    }
    props.columnCount = rs.getColumnCount();
    for(i = 1; i <= props.columnCount; i++){
        props["col" + i + "Name"] = rs.getColumnName(i);
        props["col" + i + "Type"] = rs.getColumnType(i);
    }
    var table =  getHeader(props);
    while(rs.next()){
        row = "|";
        for(c = 1; c <= props.columnCount; c++){
            row += escapeMarkup(rs.getColumnValueAsString(c)) + "|";
        }
        table += "\n" + row;
    }
    return table;

//------ End main function. Start of helper functions.

function escapeMarkup(s){
    s = s.replace(/\\/g, "\\\\");
    s = s.replaceAll('|', '\\|');
    s = s.replace(/\s+/g, " ");
    return s;
}
function getHeader(props){
    s = "|";
    for (var i = 1; i <= props.columnCount; i++){
        s += props["col" + i + "Name"] + "|";
    }
    s += "\n";
    for (var i = 1; i <= props.columnCount; i++){
        
        switch(props["col" + i + "Type"]) {
            case 'number':
                s += '|---:';
                break;
            case 'string':
                s += '|:---';
                break;
            case 'date':
                s += '|---:';
                break;
            case 'json':
                s += '|:---';
                break;
            default:
                s += '|:---:';
        }
    }
    return s + "|";
}
function isUUID(str){
    const regexExp = /^[0-9a-fA-F]{8}\b-[0-9a-fA-F]{4}\b-[0-9a-fA-F]{4}\b-[0-9a-fA-F]{4}\b-[0-9a-fA-F]{12}$/gi;
    return regexExp.test(str);
}
$$;

-- Usage type 1, a simple query:
call stackoverflow_table($$ select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION limit 5 $$);

-- Usage type 2, a query ID:
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION limit 5;
set quid = (select last_query_id());
call stackoverflow_table($quid);

Edit: Based on Fieldy's helpful feedback, I modified the procedure code to allow passing null or 0 or a blank string '' as the parameter. This will use the last query ID and is a helpful shortcut. It also adds a constant to the code that will limit the returns to a set number of rows. This limit will be applied when using query IDs (or sending null, '', or 0, which uses the last query ID). The limit is not applied when the input parameter is the text of a query to run to avoid syntax errors if there's already a limit applied, etc.

Greg Pavlik's Javascript Stored Procedure solution made me wonder if this would be any easier with the new Python language support in Stored Procedures. This is currently a public-preview feature.

The Python Snowpark API supports returning a result as a Pandas dataframe, and Pandas supports returning a dataframe in Markdown format, via the tabulate package. Here's the stored procedure.

CREATE OR REPLACE PROCEDURE markdown_table(query_id VARCHAR)
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python','pandas','tabulate', 'regex')
HANDLER = 'markdown_table'
EXECUTE AS CALLER
AS $$
import pandas as pd
import tabulate 
import regex

def markdown_table(session, queryOrQueryId =  None):
   # Validate UUID
   if(queryOrQueryId is None):
          pandas_result = session.sql("""Select * from table(result_scan(last_query_id()))""").to_pandas()
   elif(bool(regex.match("^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$", queryOrQueryId))):
          pandas_result = session.sql(f"""select * from table(result_scan('{queryOrQueryId}'))""").to_pandas()
   else:
          pandas_result = session.sql(queryOrQueryId).to_pandas()    
          
   return pandas_result.to_markdown() 
$$;

Which you can use as follows:

-- Usage type 1, use the result from the query ran immediately proceeding the Store-Procedure Call
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION limit 5;
call markdown_table(NULL);

-- Usage type 2, pass in a query_id
select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION limit 5;
set quid = (select last_query_id());
select $quid;
call markdown_table($quid);

-- Usage type 3, provide a Query string to the Store-Procedure Call
call markdown_table('select * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION limit 5');

The table can also be

N_NATIONKEY|N_NAME|N_REGIONKEY
--|--|--
0|ALGERIA|0
1|ARGENTINA|1
2|BRAZIL|1
3|CANADA|1
4|EGYPT|4

giving, so it can be a simpler solution

I grab the result table and use notepad++ and replace tab \t with pipe space | and then insert by hand the header marker line. I sometime replace the empty null results with the text null to make the results make more sense. the form you use with the start/end pipes gets around the need for that.

DBeaver IDE supports "data export as markdown" and "advanced copy as markdown" out-of-the-box:

Output:

|R_REGIONKEY|R_NAME|R_COMMENT|
|-----------|------|---------|
|0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |
|1|AMERICA|hs use ironic, even requests. s|
|2|ASIA|ges. thinly even pinto beans ca|
|3|EUROPE|ly final courts cajole furiously final excuse|
|4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl|

It is rendered as: