CODEWITHSUNDEEP
rgreenplumplr
Shubham Saini
Shubham Saini

Reputation: 738

PL/R - Pass entire column as an argument

I am trying to write a simple PL/R function that finds the mean of a column (Greenplum 4.3.4 DB)

CREATE OR REPLACE FUNCTION mean_plr(val numeric[]) RETURNS numeric AS
$$
x <- val
avg = mean(x)
return(avg)
$$
LANGUAGE 'plr';

SELECT mean_plr(sensor1) FROM public.tempSensors;

This, however, gives me the error:

ERROR:  function mean_plr(numeric) does not exist
LINE 1: SELECT mean_plr(sensor1) FROM public.tempSensors;
               ^
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Upvotes: 1

Views: 121

Answers (1)

Shubham Saini
Shubham Saini

Reputation: 738

Figured out what I was doing wrong. mean_plr(sensor1) sends 1 value at a time. To send entire column, we need to use array_agg() function.

SELECT mean_plr(array_agg(sensor1::numeric)) FROM public.tempSensors;

Upvotes: 1

Related Questions