Re: my first procedure

On 8/10/06, marcelo Cortez <jmdc_marcelo(at)yahoo(dot)com(dot)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