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

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;