CODEWITHSUNDEEP
arrayspostgresqlplpgsql
glicuado
glicuado

Reputation: 1593

how to load a query into arrays

I need to load some queries into vectors to avoid temporary tables in a function:

create table mytable
 ( eref  serial primarykey,
   edia  date,
   eimpte numeric);
 ---

CREATE OR REPLACE FUNCTION peps(rseller integer)
  RETURNS void AS
$BODY$
declare 
  dep_dia    date[]   := '{}';
  dep_impte  numeric[]:= '{}';
  dep_ref    integer[]:= '{}';
  ndepositos integer  :=0;
  rec        record;
begin 

for rec in 
    select eref, edia, eimpte from mytable order by edia, eimpte 
loop
      ndepositos:=ndepositos+1;
      dep_dia[ndepositos]  :=edia;
      dep_impte[ndepositos]:=eimpte;
      dep_ref[ndepositos]  :=eref;    
end loop; 
raise notice ' ndeps %', ndepositos;
end
$BODY$
language plpgsql volatile;

it does not work:

ERROR:  column "edia" does not exist
LINE 1: SELECT edia
               ^

what am I doing wrong?

Thanks in advance

Upvotes: 0

Views: 48

Answers (2)

bobflux
bobflux

Reputation: 11581

Don't loop! Postgres provides a great function for this:

SELECT array_agg(eref), array_agg(edia), array_agg(eimpte)
FROM (SELECT * from mytable order by edia, eimpte) AS foo
INTO your variables

By putting the order by in a subquery, the aggregate functions will get the values in the order you want. This should be faster than looping.

Upvotes: 1

vatsal mevada
vatsal mevada

Reputation: 5616

You are not having edia column while creating the table. Looking at reamining code, I feel your create table query should be like following:

create table mytable
 ( edia serial primarykey,
   eref  date,
   eimpte numeric);

Upvotes: 0

Related Questions