PostgreSQL Dynamic SQL – Fix Dynamic UPDATE Fails Due to Unwanted Parenthesis

dynamic-sqlplpgsqlpostgresqlupdate

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app.

I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: (ed. fixed code after answer below)

do $$
declare
target text;
begin
    for target in
        select quote_ident(column_name)
        from information_schema.columns
        where table_name = 'cds' and data_type = 'character varying'
    loop
        RAISE NOTICE 'Calling trim(%)', target;
        execute 'update cds set '
            || quote_ident(target)
            || ' = trim('
            || quote_ident(target)
            || ')';
    end loop;
end
$$;

The select gives my a nice list of all the column names in my table and I was hoping to for-loop through all of those: 

gakera=# select column_name from information_schema.columns
         where table_name = 'cds' and data_type = 'character varying';

 column_name 
-------------
 userID
 junk1
 dates
 times
 junk2
 junk3
 servID
 junk4
 junk5

Sidetrack

Not the question, but just extra confusing.

The fun starts at \i ~/path/test.sql with this error: 

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  function quote_ident(record) does not exist
LINE 2:    || quote_ident(target)
              ^
HINT:  No function matches the given name and argument types.
       You might need to add explicit type casts.

What's going on with quote_ident? I'm gonna try a sanity check, I copied this from http://www.postgresql.org/docs/9.3/static/functions-string.html

gakera=# select quote_ident('test');
 quote_ident 
-------------
 test

Ok, insanity check then: 

gakera=# select quote_indeed('test');
ERROR:  function quote_indeed(unknown) does not exist
LINE 1: select quote_indeed('test');
               ^
HINT:  No function matches the given name and argument types.
       You might need to add explicit type casts.

Ugh, whatever, I'll just remove the quote_identfunction from the offending lines (it didn't complain about the select quote_ident part so I keep that. Now I have: 

    execute 'update cds set '
        || target
        || ' = trim('
        || target
        || ')';

Question

Here is where things start to get interesting. when I run this like before I get: 

psql:/path/test.sql:18: NOTICE:  Calling trim((anum))
psql:/path/test.sql:18: ERROR:  syntax error at or near "trim"
LINE 1: update cds set (anum) = trim((anum))
                                 ^
QUERY:  update cds set (anum) = trim((anum))
CONTEXT:  PL/pgSQL function inline_code_block line 11 at EXECUTE statement

Why is it adding () around anum? How can I get rid of them? The EXECUTE statement should read update cds set anum = trim(anum)

Best Answer

The first listed function is almost correct. Variable target should be of type text. Try this:

do $$
declare
    target text;
begin
    for target in
        select column_name
        from information_schema.columns
        where table_name = 'cds' and data_type = 'character varying'
    loop
        RAISE NOTICE 'Calling trim(%)', target;
        execute 'update cds set '
            || target
            || ' = trim('
            || target
            || ')';
    end loop;
end
$$;
Related Question