Re: error creating sql function

"Matthew T. O'Connor" <matthew(at)zeut(dot)net> writes:
> I was trying to create a sql function today (see below) using
> postgresql 7.3.3. I don't see how to get around this error, anyone
> have any suggestions?

> tocr=# CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar)
> ...
> tocr'# copy do_not_call_list (area_code, number) from $2 with delimiter
> ...
> tocr-# LANGUAGE 'sql' VOLATILE;
> ERROR: parser: parse error at or near "$2" at character 178

COPY, like all the other utility commands in Postgres, doesn't support
$n parameters. (Basically, you can only use these where an expression
would be allowed, which is only in SELECT/INSERT/UPDATE/DELETE.)

You can work around this by constructing the desired command as a string
in plpgsql or one of the other PL languages, say

CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar)
...
execute ''copy do_not_call_list (area_code, number) from '' || quote_literal($2) || '' with delimiter ''
...
LANGUAGE 'plpgsql' VOLATILE;

regards, tom lane