PL/pgSQL - Sytax Error near $1 ?

From: Emre Bastuz <info(at)emre(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: PL/pgSQL - Sytax Error near $1 ?
Date: 2005-10-05 20:36:01
Message-ID: 1128544561.434439312827c@netmail1.netcologne.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message George McQuade 2005-10-05 22:45:45 Schema handling within plpgsql
Previous Message operationsengineer1 2005-10-05 20:34:28 Promising PGSQL / Window Install Tutorial