Re: Select of a function that returns a array

CREATE or replace FUNCTION mean_and_variancepop(var_table varchar,
var_col varchar) RETURNS float8[] AS $$
DECLARE
a float;
b float;
BEGIN
a = 1.02;
b =3.05;
RETURN ARRAY[a,b];

END;
$$ LANGUAGE plpgsql;

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Listas Evandro (Não
use este endereço para mensagens pessoais)
Sent: Thursday, May 05, 2005 8:08 PM
To: pgsql-sql
Subject: [SQL] Select of a function that returns a array

Hi guys

I'm trying to figure out a way to make this select work even for a wrong value:

select mean_and_variancepop('golf', 'temperature');
or
select CAST (mean_and_variancepop('golf', 'temperature') AS float(2));

This function return an array

the function is:

CREATE or replace FUNCTION mean_and_variancepop(var_table varchar,
var_col varchar) RETURNS float(2) AS $$
DECLARE
curs refcursor;
a float;
b float;
c float;
retvals float[2];
sum float:=0;
BEGIN
open curs FOR EXECUTE 'SELECT ' || quote_ident(var_col) || ' FROM
' || quote_ident(var_table);
b:=0;

LOOP
-- some computations
FETCH curs INTO a;

IF NOT FOUND THEN
EXIT; -- exit loop
END IF;
b:=b+1;
c:=a;
sum:= sum + c;
END LOOP;
CLOSE curs;
retvals[0]:= sum / b;
retvals[1] :=4;
RETURN retvals;

END;
$$ LANGUAGE plpgsql;

Thanks
-Jr

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match