Thread: PL/pgSQL - Sytax Error near $1 ?
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
This is a guess from a novice as well (untested) but your function is told
to return a INT in the opening call, but the RETURN statement at the end of
your function isn't returning anything:
FUNCTION traffic_add (text, integer, text) RETURNS INT AS
blah, blah
RETURN; << I think this needs to return an INT as stated above.
END;
Hope this helps,
Derrick
----- Original Message -----
From: "Emre Bastuz" <info@emre.de>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, October 05, 2005 2:36 PM
Subject: [NOVICE] PL/pgSQL - Sytax Error near $1 ?
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
On Oct 5, 2005, at 4:36 PM, Emre Bastuz wrote: > 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; It appears you are passing table_name as a parameter. You can't use table_name like this (as a string) except as part of an EXECUTE statement. You might some useful hints in the example code included with pgEdit (http://pgedit.com/download). The distribution includes an extensive example for HTTP logging and analysis (see the examples/web folder). Below is a function from the example that inserts an address in the database by IP number or domain name. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL -- Checks to see if the address is already in the database. If not a new address is created. -- Returns the database id of the existing or new address. create or replace function web.address_dbid (addr_name text, addr_ip inet) returns integer as $$ declare d_name text; addr_dbid integer; begin if addr_name is not null then d_name := lower(addr_name); end if; if d_name is null and addr_ip is null then raise exception 'the domain name or ip address must be provided.'; elsif d_name is not null and addr_ip is not null then select into addr_dbid dbid from web.address where domain_name = d_name and ip = addr_ip; elsif d_name is not null then select into addr_dbid dbid from web.address where domain_name = d_name; else select into addr_dbid dbid from web.address where ip = addr_ip; end if; if addr_dbid is null then insert into web.address (domain_name, ip) values (d_name, addr_ip); addr_dbid := currval(pg_get_serial_sequence('web.address', 'dbid')); end if; return addr_dbid; end; $$ language plpgsql;