On 8/10/06, marcelo Cortez <jmdc_marcelo@xxxxxxxxxxxx> 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
