pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Select of a function that returns a array
3+ messages / 2 participants
[nested] [flat]

* Select of a function that returns a array
@ 2005-05-05 14:27  Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Listas Evandro (Não use este endereço para mensagens pessoais) @ 2005-05-05 14:27 UTC (permalink / raw)
  To: pgsql <pgsql-general@postgresql.org>

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Select of a function that returns a array
@ 2005-05-05 14:38  Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr@gmail.com>
  parent: Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Listas Evandro (Não use este endereço para mensagens pessoais) @ 2005-05-05 14:38 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Select of a function that returns a array
@ 2005-05-06 04:46  Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Ramakrishnan Muralidharan @ 2005-05-06 04:46 UTC (permalink / raw)
  To: Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr@gmail.com>; pgsql-sql

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@postgresql.org
[mailto:pgsql-sql-owner@postgresql.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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-05-06 04:46 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-05 14:27 Select of a function that returns a array Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr@gmail.com>
2005-05-05 14:38 ` Listas Evandro (Não use este endereço para mensagens pessoais) <listasjr@gmail.com>
2005-05-06 04:46 Re: Select of a function that returns a array Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox