Use Aggregate Function on Unnested Array in Postgres?

Trying to use Postgres var_samp() function on multiple columns in my db and want to do it without subqueries/CTEs, desire speed.

I’ve tried this:

select
var_samp(array[obs1,obs2]) as varsample
    from my_table

Tried this based off another answer, couldn’t get it to work (I’m sure I’m the issue):

select
var_samp(my_col)
    from (select unnest(array[obs1, obs2])as my_col from my_table)

I’ve had success is using CTEs, but trying to avoid them:

WITH cte_raw_data as (
        select
            unnest(array[obs1, obs2])as my_col
        from my_table
        ),
cte_var_samp as (
        select
            var_samp(my_col) as my_var_samp
        from cte_raw_data   
)
SELECT * FROM cte_var_samp

my_table data below, thank you for helping me:

>Solution :

You can simplify that query to

WITH cte_raw_data AS (
    SELECT unnest(array[obs1, obs2]) AS my_col
    FROM my_table
)
SELECT var_samp(my_col) AS my_var_samp
FROM cte_raw_data;

or

SELECT var_samp(my_col) AS my_var_samp
FROM (
    SELECT unnest(array[obs1, obs2]) AS my_col
    FROM my_table
) AS cte_raw_data;

but really you just want to use unnest as a lateral table function:

SELECT var_samp(my_col) AS my_var_samp
FROM my_table, unnest(array[obs1, obs2]) AS my_col;

Notice that all these queries are doing the same thing and will have the same query plan and same execution speed.