SEO

folks

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

any pointer be apreciated


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';


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

any clue?

best regards
MDC






__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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