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@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
>
>