Re: my first procedure

On 8/10/06, marcelo Cortez <jmdc_marcelo@yahoo.com.ar> 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