pl/pgsql array as input to an aggregate function

Question

i am writing pl/pgsql function that does some statistics processing. using 8.2 of postgres. i want to use this handy aggregate function:

regr_slope(Y, X)

but, i have my X and Y data stored as local arrays in the pl/pgsql function: y double precision[]; x double precision[];

trouble is when i use this as a line in the pl/pgsql function:

slope := regr_slope(y, x);

i get an error saying that function isn't available or has the wrong arguments. i suspect it's because the inputs are supposed to be selected as columns from a table instead of being passed as double precision arrays.

is there a way to make the regr_slope function with local arrays? (ie, some way to cast the array to be a valid input to an aggregate function?)

thank you.

Milen A. Radev · Accepted Answer · 2010-10-23 21:59:56Z

2

SELECT regr_slope(x,y) INTO slope FROM (SELECT unnest(ARRAY[1,2,3,4]) as x, unnest(ARRAY[5,6,7,8]) AS y) AS z;

answered Oct 23, 2010 at 21:59

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jeff Over a year ago

thanks. this worked for me with one addition. i needed to add in the unnest function:

jeff Over a year ago

DROP FUNCTION unnest(anyarray); CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM generate_series(array_lower($1,1),array_upper($1,1)) i; $BODY$ LANGUAGE 'sql' VOLATILE COST 100 ROWS 1000; ALTER FUNCTION unnest(anyarray) OWNER TO postgres;

Collectives™ on Stack Overflow

pl/pgsql array as input to an aggregate function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related