Re: error creating sql function - Mailing list pgsql-general

From Tom Lane
Subject Re: error creating sql function
Date
Msg-id 13998.1074315920@sss.pgh.pa.us
Whole thread Raw
In response to error creating sql function  ("Matthew T. O'Connor" <matthew@zeut.net>)
List pgsql-general
"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

pgsql-general by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: New PostgreSQL search resource
Next
From: David Garamond
Date:
Subject: Re: YAGT (yet another GUID thread)