The problem is solved. It is a silly mistake actually. The line
SELECT sid FROM conf_category WHERE
category_name like temp_category and category_status=1;returns duplicated values. Variable temp_category would be '%dos.rules' and there are entries 'dos.rules' and 'ddos.rules' in table conf_category which I did not notice until today. So I add the keyword DISTINCT and it runs perfectly.
select * into temp_category from get_root_path(category);
OPEN T1Cursor FOR
SELECT DISTINCT sid FROM conf_category WHERE
category_name like temp_category and category_status=1;Josh Berkus wrote:
I am porting this application from ORACLE. So I try my best not to change the structure of the original codes. I guess it is still not a good excuse for the way I name the variable :). Anyway thanks Josh.Khairul,
Need help on this problem. I've created two functions. The function should perform a few sql processes. The problem is I got different results when I call the function and when I manually run the sql command.
I'm using postgresql 7.4.2.
Hmmm ... that's odd. I remember getting this issue early in the 7.2 series but not since. I'd guess that you're missing something in your function, like the transposition of two fields or an unterminated loop. You've made that likely because:
RETURNS integer AS '
DECLARE
var_f0 alias FOR $1; -- rh_sign_id
var_f1 alias FOR $2; -- rh_status
var_f2 alias FOR $3; -- rh_action
var_f3 alias FOR $4; -- proto
... this is a really bad way of dealing with function variables; I certainly can't parse the rest of the function and tell if you've accidentally swapped a var_f3 for a var_f4. I'd strongly suggest naming your variables clearly, like, for example, calling it "v_proto" instead of "var_f3". This is "programming 101".
Issue is cews=> select rule_header_add(999,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any', '->','dos.rules',3,0); NOTICE: INSERT INTO rule_header VALUES 999 1 alert ip $EXTERNAL_NET any $HOME_NET any -> dos.rules 3 2004-06-04 15:21:30.448633 NOTICE: INSERT INTO sensor_signature VALUES -1 999 CONTEXT: PL/pgSQL function "rule_header_add" line 26 at perform ERROR: duplicate key violates unique constraint "sensor_signature_pkey" CONTEXT: PL/pgSQL function "update_sen_sig" line 16 at SQL statement PL/pgSQL function "rule_header_add" line 26 at perform
I thought it might be caused by duplicated data. But ... cews=> insert into rule_header values (268,1,'alert','ip','$EXTERNAL_NET','any','$HOME_NET','any','->','dos.rules ',3,current_timestamp,0); INSERT 29393 1
And
cews=> insert into sensor_signature values (-1,268);
INSERT 29394 1
This isn't the same id you tested with the function. Mind running the *exact same values* with both command line and function?
Also, I notice that update_sen_sig makes use of a cursor and a loop. Best guess is that the cursor isn't returning what you think it is, and is looping several times ... thus attempting to insert the same value several times.
Good luck!
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
