Thread: COPY with a variable path
I want to execute the following COPY command in a stored procedure, however, I need to pass the path to the file as an input parameter. Substituting the input parameter for the literal path does not work and neither does using PREPARE/EXECUTE. How can I pass the file path as a parameter? copy contact.palm_import from 'c:/_junk/palm.csv' with delimiter as ',' null as '' csv quote as '"'; Thanks, Bill
On 27/11/2008 20:52, Bill Todd wrote: > Substituting the input parameter for the literal path does not work and > neither does using PREPARE/EXECUTE. How can I pass the file path as a > parameter? You could write a pl/pgsql function which constructs the query as a string and then runs it with EXECUTE. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Raymond O'Donnell wrote:
EXECUTE AStringVariable;
where AStringVariable contains a valid SQL statement? If so, that is the solution.
Bill
According to the PostgreSQL help file EXECUTE is used to execute a prepared statement. I tried that but when I call PREPARE with COPY as the statement I get an error. Are you saying that I can also use EXECUTE asOn 27/11/2008 20:52, Bill Todd wrote:Substituting the input parameter for the literal path does not work and neither does using PREPARE/EXECUTE. How can I pass the file path as a parameter?You could write a pl/pgsql function which constructs the query as a string and then runs it with EXECUTE. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
EXECUTE AStringVariable;
where AStringVariable contains a valid SQL statement? If so, that is the solution.
Bill
Raymond O'Donnell wrote:
Bill
I tried your suggestion and it worked! Thanks.On 27/11/2008 20:52, Bill Todd wrote:Substituting the input parameter for the literal path does not work and neither does using PREPARE/EXECUTE. How can I pass the file path as a parameter?You could write a pl/pgsql function which constructs the query as a string and then runs it with EXECUTE. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Bill
On 27/11/2008 23:09, Bill Todd wrote: > Raymond O'Donnell wrote: >> You could write a pl/pgsql function which constructs the query as a >> string and then runs it with EXECUTE. >> > According to the PostgreSQL help file EXECUTE is used to execute a > prepared statement. I tried that but when I call PREPARE with COPY as > the statement I get an error. Are you saying that I can also use EXECUTE as Sorry, Bill, I wasn't clear enough - I was suggesting writing a pl/pgsql function something like this (not tested): create function do_copy(path text) returns void as $$ begin execute 'copy your_table from ' || filepath; return; end; $$ language plpgsql; EXECUTE in pl/pgsql is different from the version you refer to above....here it is in the docs: http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------