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_r ef_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_p art_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 ][ hl****@medium13 .sk ][ http://www.medium13.sk ]
[ icq:94900232 ][ callto://hlavki ]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
