PL/pgSQL to copy data from tables dynamically

Question

I'm trying to write some SQL to copy data from all of my PostgreSQL tables in a given database based on what's in the information_schema. It should output data files to my local machine ready for import to another machine. Ultimately, I'm going to tweak this so that I dump only select portions of tables (some of the tables I'm dumping have millions of records and I only want a small subset of data for testing purposes).

Here's what I have so far...

--Copy all tables...
DO
$$
DECLARE
    formatstring text;
    rec record;
BEGIN
    RAISE NOTICE 'Copying tables...';
    formatstring = 'COPY (select * from %I) to ''C:\Media\Code\%s.csv'';';
    FOR rec IN 
        select table_name from information_schema.tables where table_schema = 'public' order by table_name
    LOOP
        RAISE NOTICE 'Table: %', rec.table_name;
        RAISE NOTICE format(formatstring, rec.table_name, rec.table_name);
        EXECUTE format(formatstring, rec.table_name, rec.table_name);
    END LOOP;
END;
$$
LANGUAGE plpgsql;

However, I am getting this exception...

ERROR:  unrecognized exception condition "format"
CONTEXT:  compilation of PL/pgSQL function "inline_code_block" near line 12
********** Error **********

ERROR: unrecognized exception condition "format"
SQL state: 42704
Context: compilation of PL/pgSQL function "inline_code_block" near line 12

The escaping of the single quotes seems fine (already checked this question: Insert text with single quotes in PostgreSQL). Indeed I can do the following and it works, with text being inserted into the formatting:

select format('COPY (select * from %I) to ''C:\Media\Code\%s.csv'';', 'system_user', 'system_user');

Can anyone assist with this issue? I can easily write a script or code that will generate the copy commands for me, but it would be great to do it all within a simple bit of SQL.

Answer 1

The cause is a syntax error in your 3nd RAISE statement. There are several valid formats, but you cannot feed an expression to RAISE directly. It has to be a string literal - with the option of string interpolation.

While being at it, simplify a couple of other things:

DO
$do$
DECLARE
   _sql text;
   _tbl text;
BEGIN
   RAISE NOTICE 'Copying tables...';
   
   FOR _tbl IN 
      SELECT table_name
      FROM   information_schema.tables
      WHERE  table_schema = 'public'
      ORDER  BY table_name
   LOOP
      _sql := format($$COPY %1$I TO 'C:\Media\Code\%1$s.csv'$$, _tbl);
      RAISE NOTICE 'Table: %', _tbl;
      RAISE NOTICE '%', _sql;            -- fixed!
      EXECUTE _sql;
   END LOOP;
END
$do$;

• Use the plain table name with COPY instead of SELECT * FROM tbl.
• Use nested dollar-quotes.
• Format specifiers %1$I and %1$s for the format() function, so we only need to supply the table name once.
• Use a scalar variable instead of a record in the FOR loop - we only need the one column anyway.