current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite
1

This PostgreSQL COPY command works:

copy tablename from E'c:\\abc\\a.txt';

but I want the tablename to be dynamically generated. How can I do this?

share|improve this question

1 Answer 1

up vote 11 down vote accepted

You need to build a string, concatenating in the dynamic table name, and then use execute. Note that you escape the ' by ''. This also includes a dynamic name to save the file too. You need to replace savedir with the actual directory you are using. 

create or replace function dynamicCopy(tablename text, outname text) returns void AS $$

declare STATEMENT TEXT;

begin

  STATEMENT := 'COPY (select * from ' || quote_ident(tablename) || ') to ''savedir' || outname ||'.txt''';

  EXECUTE STATEMENT;

end;

$$ LANGUAGE 'plpgsql';

EDIT:

Since I first wrote this, I have discovered the format function, which I think is generally easier to read than sql generated with the concatenation operator || and has other advantages in other contexts.

create or replace function dynamicCopy(tablename text, outname text) returns void as 
$body$
begin
  execute format('copy (select * from %s) to ''savedir%s.csv''', tablename, outname);
end
$body$ language plpgsql;

See the official docs for a full discussion: http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

share|improve this answer
2  
Your example-code actually demonstrates a dynamically-generated file-name rather than a dynamically-generated table-name. Also, you don't mention that your example-code specifies comma-delimited output, rather than the default tab-delimited output. (Did you copy your example-code from somewhere else?) –  ruakh Dec 19 '11 at 17:55
    
Ruakh, thank you for pointing out the error. Now edited with dyanmic table name and file name. Yes, it is a greatly simplified version of something I use at work -- I will be more careful in future to answer the question properly. –  John Barça Dec 20 '11 at 18:43
    
Thanks for the fixes! +1 :-) –  ruakh Dec 20 '11 at 18:46
    
Jesus Christ, it took me some time to figure out the typo in quot_ident! It is quotE_ident actually! Now this one does make sense: "pg_execute(): Query failed: ERROR: 42883: function quot_ident(text) does not exist" –  Zapadlo Jan 18 '13 at 12:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.