Thread: error creating sql function
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? Thanks much, Matthew tocr=# CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar) tocr-# RETURNS void AS tocr-# ' tocr'# begin; tocr'# update area_codes tocr'# set last_updated = now() tocr'# where code = $1; tocr'# DELETE from do_not_call_list tocr'# where area_code = $1; tocr'# copy do_not_call_list (area_code, number) from $2 with delimiter as \',\'; tocr'# commit; tocr'# ' tocr-# LANGUAGE 'sql' VOLATILE; ERROR: parser: parse error at or near "$2" at character 178 tocr=# COMMENT ON FUNCTION public.update_dncl(bpchar, bpchar) IS 'Will be used to update an area code in the DNCL tables.'; COMMENT tocr=# SELECT version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) (1 row)
Matthew T. O'Connor wrote: > 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? > > Thanks much, > > Matthew > > tocr=# CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar) > tocr-# RETURNS void AS > tocr-# ' > tocr'# begin; > tocr'# update area_codes > tocr'# set last_updated = now() > tocr'# where code = $1; > tocr'# DELETE from do_not_call_list > tocr'# where area_code = $1; > tocr'# copy do_not_call_list (area_code, number) from $2 with delimiter > as \',\'; > tocr'# commit; > tocr'# ' > tocr-# LANGUAGE 'sql' VOLATILE; > ERROR: parser: parse error at or near "$2" at character 178 > tocr=# COMMENT ON FUNCTION public.update_dncl(bpchar, bpchar) IS 'Will > be used to update an area code in the DNCL tables.'; > COMMENT Quick reply ... I haven't tested this, and it's only a theory, so treat it as such. The copy command should have '' around the filename, so possibly: copy do_not_call_list (area_code, number) from ''$2'' with delimiter as \',\'; ... would work? > tocr=# SELECT version(); > > version > --------------------------------------------------------------------------------------------------------- > > PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 > 20020903 (Red Hat Linux 8.0 3.2-7) > (1 row) > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bill Moran Potential Technologies http://www.potentialtech.com
"Matthew T. O'Connor" <matthew@zeut.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