Re: [GENERAL] my first procedure

[Merlin Moncure]

On 8/10/06, marcelo Cortez <[EMAIL PROTECTED]> wrote:

folks

 i´ts is my first procedure/function
 the pgadmin show 10 secs in execute it

 any pointer  be apreciated

use dollar quote (pg 8.0 and up):

create or replace function fs_getstring() RETURNS TEXT as
$$
[...]
$$ language plpgsql;

CREATE OR REPLACE FUNCTION fs_getstring() RETURNS TEXT
 AS '
DECLARE
    DECLARE
       curs1 CURSOR FOR  select id_reparticion
::varchar  || chr(1) ||  codigo_reparticion ::varchar
 || chr(1)  ||
 codigo_repar_inter ::varchar || chr(1) ||
nombre_reparticion ::varchar  || chr(1) ||
vigencia_desde ::varchar
|| chr(1) || vigencia_hasta  ::varchar  ||  chr(1) ||
id_calle_repar ::varchar || chr(1) || numero ::varchar
|| chr(1) || piso ::varchar ||
chr(1) || oficina ::varchar || chr(1) ||  telefono
::varchar || chr(1) ||  fax ::varchar ||
chr(1) || email ::varchar || chr(1)  ||
codigo_estructura ::varchar  || chr(1) ||
repart_presentismo ::varchar || chr(1) ||
id_reparticion_ext ::varchar || chr(1)  ||
proximo_remito ::varchar  || chr(1) || en_red
::varchar || chr(1) ||
sector_mesa ::varchar  || chr(255) ::text
 from repartit;
    v_buffer TEXT ;
    v_var   TEXT  ;


BEGIN
       v_var = ''''  ;
       open curs1 ;

       FETCH curs1 INTO v_buffer  ;
       WHILE ( FOUND ) LOOP
            v_var = v_var ||  v_buffer ;
         FETCH  curs1 INTO v_buffer  ;
       END LOOP;
     close curs1 ;
      RETURN v_var;
END
' LANGUAGE 'plpgsql';

using your approach I like this formulation better, but that's just me:

declare
 rec record;
begin
 for rec in select id_reparticion [..] as v loop
   v_var:= v_var || v;
 end loop;
end;

 the statement
   v_var = v_var ||  v_buffer ;
 allocate memory dynamically , i think this is problem

maybe. you might try:
first, make a view on repartit to simpify this a bit:

next:
create view stringify_repartit as select id_reparticion [...] as var
 from repartit;

next:
CREATE AGGREGATE array_accum (
   sfunc = array_append,
   basetype = anyelement,
   stype = anyarray,
   initcond = '{}'
);

finally,
select array_to_string(array_accum(var), '') from stringify_repartit;

and compare.

merlin

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings