cotera-grant

   cat /usr/share/dict/words | grep 'ast'

  with cte as (select *, 'foo' as "bar" from "cotera_data".foo) select coalesce("bar", 'baz') from cte;

  with cte as (select *, 'foo'::text as "bar" from "cotera_data".foo) select coalesce("bar", 'baz') from cte;

  -- Allowed
  with bar as (select (json_parse('{"a": 1}')) as foo) select foo.a from bar;

  -- Not allowed
  select (json_parse('{"a": 1}')).a as foo

  -- [0A000] ERROR: applying array subscript on complex expression of SUPER type is currently not supported

  // Valid redshift
  // ```
  // select generate_series(0, 10);
  // ```
  //
  // Not valid redshift
  // ```
  // -- Inserts run on compute nodes
  // insert into foo (a) (
  //     -- Leader only function
  //     select generate_series(0, 10) as a
  // )
  // ```
  //
  // This is because `generate_series` is a leader only function, so it can’t be run on worker nodes
  // https://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions_leader_node_only.html
  // https://docs.aws.amazon.com/redshift/latest/dg/c_sql-functions-leader-node.html
  // https://stackoverflow.com/questions/62716606/redshift-loading-data-issue-specified-types-or-functions-one-per-info-message
  // https://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift#comment96402527_22782384
  //
  // Recurive CTEs are NOT supported in subqueries
  // ```
  // -- Not valid
  // select \* from (
  //    with recursive t(n) as (
  //        select 1::integer union all select n + 1 from t where n < 100
  //    ) select n from t
  // );
  // ```
  // To get around this, we can use the approach outlined by how dbt does ansi sql generate_series

  // https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/generate_series.sql
  const numbers = (upperBound: number) => {
    if (upperBound > 2 ** 11) {
      throw new Error(
        `We only support generating series in Reshift where the upperBound is less than ${
          2 ** 11
        }`
      );
    }

    return `
  (
    with p as (
      select 0::integer as generated_number union all select 1::integer
    ),
      unioned as (
      select
        (   p0.generated_number * power(2, 0) 
  // ... Omitted for brevity
         +  p11.generated_number * power(2, 11) 
        ) as generated_number
      from
        p as p0
        cross join p as p1
  // ... Omitted for brevity

        cross join p as p11
    )
    select generated_number::integer from unioned where generated_number <= ${upperBound} order by generated_number
  )
  `;
  };