Обсуждение: How use input parameter as path to COPY in function?
I am missing something basic. How can I use an input parameter as the destination path in a COPY statement in a function. The following fails with a syntax error at or near the parameter. CREATE OR REPLACE FUNCTION dvd.export_tables(IN export_path text) RETURNS void AS $$ BEGIN copy dvd.genre to export_path with delimiter as E'\t' null as ''; END; $$ LANGUAGE 'plpgsql' VOLATILE
Bill Todd <pg@dbginc.com> writes: > I am missing something basic. How can I use an input parameter as the > destination path in a COPY statement in a function. plpgsql can only substitute parameter values into places where a data value is called for in a DML statement (ie, SELECT/INSERT/UPDATE/DELETE). To use a parameter in other contexts, such as a utility statement like COPY, you need to construct the command as a string and EXECUTE it. Try something like EXECUTE 'copy dvd.genre to ' || quote_literal(export_path) || $q$ with delimiter as E'\t' null as '' $q$ ; (There's any number of ways to do the quoting here, of course, but I do strongly recommend using quote_literal() on the parameter.) regards, tom lane
Thanks Tom. As usual, I learned a lot more from your reply than just the answer to my question .
Bill
Tom Lane wrote:
Bill
Tom Lane wrote:
Bill Todd <pg@dbginc.com> writes:I am missing something basic. How can I use an input parameter as the destination path in a COPY statement in a function.plpgsql can only substitute parameter values into places where a data value is called for in a DML statement (ie, SELECT/INSERT/UPDATE/DELETE). To use a parameter in other contexts, such as a utility statement like COPY, you need to construct the command as a string and EXECUTE it. Try something like EXECUTE 'copy dvd.genre to ' || quote_literal(export_path) || $q$ with delimiter as E'\t' null as '' $q$ ; (There's any number of ways to do the quoting here, of course, but I do strongly recommend using quote_literal() on the parameter.) regards, tom lane