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!
--
Josh Berkus
Aglio Database Solutions
San Francisco