Select of a function that returns a array

From: Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr(at)gmail(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Select of a function that returns a array
Date: 2005-05-05 14:27:58
Message-ID: ffac5689050505072721405cb4@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Treat 2005-05-05 14:28:50 Re: [GENERAL] Interesting article on transactional algorithms includes
Previous Message Peter Wilson 2005-05-05 14:21:40 Re: postgresql replication

Browse pgsql-sql by date

  From Date Subject
Next Message Listas Evandro (Não use este endereço para mensagens pessoais) 2005-05-05 14:38:29 Select of a function that returns a array
Previous Message Richard Huxton 2005-05-05 09:21:37 Re: accessing multiple database