Re: [GENERAL] COPY with a variable path
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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY with a variable path
Raymond O'Donnell wrote: 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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- 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 EXECUTE AStringVariable; where AStringVariable contains a valid SQL statement? If so, that is the solution. Bill
Re: [GENERAL] COPY with a variable path
Raymond O'Donnell wrote: 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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- I tried your suggestion and it worked! Thanks. Bill
Re: [GENERAL] COPY with a variable path
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 abovehere 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 [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general