Re: PL/pgSQL - Sytax Error near $1 ?

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(at)emre(dot)de>
To: <pgsql-novice(at)postgresql(dot)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
>
>