hello, I have interesting problem...
I have stored procedure, which works good, but only if input is "correct".
Correct input is, when $1 is id, which exists in table...
If I put non exists id, database fall down and restart with this error:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.I think, that problem is in line:
FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path ORDER BY v_path LOOP
because without them it works well...
Version: 7.4.5 and 7.4.3 (both versions have this problem)
thanx, hlavki
source:
/*==============================================================*/
/* Table: c_part_cat */
/*==============================================================*/
create table c_part_cat (
i_part_cat_id integer default nextval('c_part_cat_seq') not null,
c_code varchar(32) not null,
v_name varchar(128) null,
v_path ltree not null,
i_parent_id int4 null,
constraint pk_c_part_cat primary key (i_part_cat_id)
);/*==============================================================*/ /* Index: index_13 */ /*==============================================================*/ create index index_13 on c_part_cat ( i_parent_id );
/*==============================================================*/ /* Index: index_22 */ /*==============================================================*/ create unique index index_22 on c_part_cat ( c_code );
/*==============================================================*/ /* Index: index_4 */ /*==============================================================*/ create index index_4 on c_part_cat using gist ( v_path );
alter table c_part_cat
add constraint fk_c_part_cat_ref_c_part_cat foreign key (i_parent_id)
references c_part_cat (i_part_cat_id)
on delete restrict on update restrict;CREATE OR REPLACE FUNCTION "public"."get_part_cat_path" (integer) RETURNS text AS'
DECLARE
my_path ltree;
result text;
tmp_row RECORD;
first bool;
BEGIN
SELECT v_path INTO my_path FROM c_part_cat WHERE i_part_cat_id = $1;
result := ''''; first := true;
FOR tmp_row IN SELECT v_name FROM c_part_cat WHERE v_path @> my_path ORDER BY v_path LOOP
IF first THEN
result := tmp_row.v_name;
first := false;
ELSE
result := tmp_row.v_name || ''->'' || result;
END IF;
END LOOP;
RETURN result;
END;
'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--
[ miso hlavac ][ [EMAIL PROTECTED] ][ http://www.medium13.sk ] [ icq:94900232 ][ callto://hlavki ]
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
