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;