github.com/lmorg/murex@v0.0.0-20240217211045-e081c89cd4ef/builtins/optional/select/select_doc.yaml (about)

     1  - DocumentID: select
     2    Title: >-
     3      `select`
     4    CategoryID: optional
     5    Summary: >-
     6      Inlining SQL into shell pipelines
     7    Description: |-
     8      `select` imports tabulated data into an in memory sqlite3 database and
     9      executes SQL queries against the data. It returns a table of the same
    10      data type as the input type
    11    Usage: |-
    12      ```
    13      <stdin> -> select * | ... WHERE ... -> <stdout>
    14  
    15      select * | ... FROM file[.gz] WHERE ... -> <stdout>
    16      ```
    17    Examples: |-
    18      List a count of all the processes running against each user ID:
    19  
    20      ```
    21      » ps aux -> select count(*), user GROUP BY user ORDER BY 1
    22      count(*) USER
    23      1       _analyticsd
    24      1       _applepay
    25      1       _atsserver
    26      1       _captiveagent
    27      1       _cmiodalassistants
    28      1       _ctkd
    29      1       _datadetectors
    30      1       _displaypolicyd
    31      1       _distnote
    32      1       _gamecontrollerd
    33      1       _hidd
    34      1       _iconservices
    35      1       _installcoordinationd
    36      1       _mdnsresponder
    37      1       _netbios
    38      1       _networkd
    39      1       _reportmemoryexception
    40      1       _timed
    41      1       _usbmuxd
    42      2       _appleevents
    43      3       _assetcache
    44      3       _fpsd
    45      3       _nsurlsessiond
    46      3       _softwareupdate
    47      4       _windowserver
    48      5       _coreaudiod
    49      6       _spotlight
    50      7       _locationd
    51      144     root
    52      308     foobar
    53      ```
    54  
    55      ```
    56  
    57      select count(*)
    58      ```
    59    Detail: |-
    60      ### Default Table Name
    61  
    62      The table created is called `main`, however you do not need to include a `FROM`
    63      condition in your SQL as Murex will inject `FROM main` into your SQL if it is
    64      missing. In fact, it is recommended that you exclude `FROM` from your SQL
    65      queries for the sake of brevity.
    66  
    67      ### `config` Options
    68  
    69      `select`'s behavior is configurable:
    70  
    71      ```
    72      » config -> [ select ]
    73      {
    74          "fail-irregular-columns": {
    75              "Data-Type": "bool",
    76              "Default": false,
    77              "Description": "When importing a table into sqlite3, fail if there is an irregular number of columns",
    78              "Dynamic": false,
    79              "Global": false,
    80              "Value": false
    81          },
    82          "merge-trailing-columns": {
    83              "Data-Type": "bool",
    84              "Default": true,
    85              "Description": "When importing a table into sqlite3, if `fail-irregular-columns` is set to `false` and there are more columns than headings, then any additional columns are concatenated into the last column (space delimitated). If `merge-trailing-columns` is set to `false` then any trailing columns are ignored",
    86              "Dynamic": false,
    87              "Global": false,
    88              "Value": true
    89          },
    90          "print-headings": {
    91              "Data-Type": "bool",
    92              "Default": true,
    93              "Description": "Print headings when writing results",
    94              "Dynamic": false,
    95              "Global": false,
    96              "Value": true
    97          },
    98          "table-includes-headings": {
    99              "Data-Type": "bool",
   100              "Default": true,
   101              "Description": "When importing a table into sqlite3, treat the first row as headings (if `false`, headings are Excel style column references starting at `A`)",
   102              "Dynamic": false,
   103              "Global": false,
   104              "Value": true
   105          }
   106      }
   107      ```
   108  
   109      (See below for how to use `config`)
   110  
   111      ### Read All vs Sequential Reads
   112  
   113      At present, `select` only supports reading the entire table from STDIN before
   114      importing that data into sqlite3. There is some prototype code being written to
   115      support sequential imports but this is hugely experimental and not yet enabled.
   116  
   117      This might make `select` unsuitable for large datasets.
   118  
   119      ### Early Release
   120  
   121      This is a very early release so there almost certainly will be bugs hiding.
   122      Which is another reason why this is currently only an optional builtin.
   123  
   124      If you do run into any issues then please raise them on [Github](https://github.com/lmorg/murex/issues).
   125    Synonyms:
   126      - select
   127    Related:
   128      - v2.1
   129      - csv
   130      - jsonl
   131      - generic
   132      - config