Is it possible to pass list of columns (in a range styled way) as an argument inside of SQL function?

Question

I'm not even sure if this is a viable question as I couldn't find anything similar on here or on Google.

I'm sure you've all seen question saying something like "How to select all columns expect few in SQL?". I didn't really any answers that were given as I can't use dynamic SQL in Vertica (can't use = not enabled).

I was thinking of making a UDF in Vertica SDK in Python, but I'm not sure if I can even make something like this, or better yet, if it even "legal" in SQL:

SELECT myFunction([0-50]) FROM my_table t

where [0-50] would select all columns 1 through 51.

Something similar in PySpark would be like this: df_exclude = df.select([cols for cols in df.columns if cols not in first_col]).collect()

Answer 1

You can try SQL generating SQL: the "report" of the coded query is the script you will run as the actual query.

Here, I want to select all columns except the first 50 of the table dbadmin.covid:

\a
-- out Output format is unaligned.
\t
-- out Showing only tuples.
WITH
collist AS (
  SELECT
    table_schema
  , table_name
  , ordinal_position
  , column_name
  , ROW_NUMBER() OVER rn AS ind
  , COUNT(*)     OVER ct AS colcount
  FROM columns
  WHERE table_schema='dbadmin' AND table_name='covid'
    AND ordinal_position > 50 -- exclude the first 50 columns
  WINDOW
   ct AS (PARTITION BY table_schema,table_name) 
  ,rn AS (ORDER BY ordinal_position)
)
SELECT 
  CASE ind
    WHEN 1 THEN 'SELECT'||CHR(10)||'  '
    ELSE        ', '
  END
||column_name
||CASE ind 
   WHEN colcount THEN CHR(10)||'FROM '||table_schema||'.'||table_name||';'
   ELSE ''
  END
FROM collist
ORDER BY ind;
-- out SELECT
-- out   median_age
-- out , aged_65_older
-- out , aged_70_older
-- out , gdp_per_capita
-- out , extreme_poverty
-- out , cardiovasc_death_rate
-- out , diabetes_prevalence
-- out , female_smokers
-- out , male_smokers
-- out , handwashing_facilities
-- out , hospital_beds_per_thousand
-- out , life_expectancy
-- out , human_development_index
-- out , excess_mortality_cumulative_absolute
-- out , excess_mortality_cumulative
-- out , excess_mortality
-- out , excess_mortality_cumulative_per_million
-- out FROM dbadmin.covid;