Hi,
I´m trying to collect IP traffic in a Postgres database and created a table
consisting of an index, a column for the source IP-address of the traffic and a
column for the counter of the transferred bytes.
While collecting the traffic info I´d like to see if a certain IP address
already has an entry in the DB and if not, insert the traffic data. In case
there is already traffic registered for the IP I´d like to select the counter-
value, add the new bytes counter and update the table entry.
I tried to accomplish this with PL/pgSQL but get an error "Syntax error near
$1":
CREATE OR REPLACE FUNCTION traffic_add (text, integer, text) RETURNS INT AS '
DECLARE
source_ip ALIAS FOR $1;
num_counter ALIAS FOR $2;
table_name ALIAS FOR $3;
logrec RECORD;
BEGIN
SELECT INTO logrec * FROM table_name WHERE sourceValue = source_ip;
IF NOT FOUND THEN
INSERT INTO table_name (sourceValue, counterValue) VALUES (source_ip
num_counter);
ELSE
new_num_counter := logrec.counterValue + num_counter;
UPDATE table_name set counterValue = new_num_counter WHERE idx = logrec.idx;
END IF;
RETURN;
END;
' LANGUAGE plpgsql;
To use the same code for different types of traffic collections (bytes by source
ip, bytes by destination ip, etc.) and thus different tables, I tried to
parametrize the table name.
Executing the above code with 'select traffic_add('192.168.0.1', 5,
'num_bytes_by_src_ip');' results in the error message 'Syntax error at or near
$1'.
Anyone have an idea what I´ve done wrong?
Regards,
Emre
--
http://www.emre.de UIN: 561260
PGP Key ID: 0xAFAC77FD
I don't see why some people even HAVE cars. -- Calvin