error creating sql function

[Matthew T. O'Connor]

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_d ncl(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_lis t
tocr'# where area_code = $1;
tocr'# copy do_not_call_lis t (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_d ncl(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)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bill Moran]

Re: error creating sql function

Matthew T. O'Connor wrote:[color=blue]
> 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_d ncl(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_lis t
> tocr'# where area_code = $1;
> tocr'# copy do_not_call_lis t (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_d ncl(bpchar, bpchar) IS 'Will
> be used to update an area code in the DNCL tables.';
> COMMENT[/color]

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_lis t (area_code, number) from ''$2'' with delimiter as \',\';

.... would work?
[color=blue]
> 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
>[/color]


--
Bill Moran
Potential Technologies

http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: error creating sql function

"Matthew T. O'Connor" <matthew@zeut.n et> writes:[color=blue]
> 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?[/color]
[color=blue]
> tocr=# CREATE OR REPLACE FUNCTION public.update_d ncl(bpchar, bpchar)
> ...
> tocr'# copy do_not_call_lis t (area_code, number) from $2 with delimiter
> ...
> tocr-# LANGUAGE 'sql' VOLATILE;
> ERROR: parser: parse error at or near "$2" at character 178[/color]

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_d ncl(bpchar, bpchar)
....
execute ''copy do_not_call_lis t (area_code, number) from '' || quote_literal($ 2) || '' with delimiter ''
....
LANGUAGE 'plpgsql' VOLATILE;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html