1

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)

  • Well I'm even dumber than I thought, since what I was trying to do is trim whitespace from just a few columns at once, this general method is complete overkill, I could have just updated multiple columns at once: update cds set col1=trim(col1), col2=trim(col2), ... ;. Maybe the general method above can help someone with a lot of columns, hopefully. – gakera May 16 '14 at 3:03
  • There is even shorter syntax for updating multiple columns: update cds set (col1, col2, ...) = (trim(col1), trim(col2), ...) Also simplifies dynamic concatenation of the SQL string - I make use of this in my answer. – Erwin Brandstetter May 16 '14 at 3:59
2

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
$$;
  • omfg I can't believe it was that simple! It seems to be working now, thanks! – gakera May 15 '14 at 21:38
  • wait, so the quote_ident error was just a roundabout way of saying "function quote_ident not defined for variable type record"? I guess reading the error now I get it, but it could be clearer... No function matches the given name and argument types. You might need to add explicit type casts. - I might no such thing! – gakera May 15 '14 at 21:41
  • You probably overlooked ERROR: function quote_ident(record) does not exist. – klin May 15 '14 at 21:46
  • Yea, I guess, at least I didn't connect it to the variable type. I'm still getting used to having to declare them explicitly, I've been spoiled by lazy languages in the past. As I said, I'm trying my best not to be a dumbass, but so far ... well ... – gakera May 15 '14 at 22:06
3

@klin already addressed the bug you had been asking about. I am going to address your general approach.

Your DO statement would run a separate UPDATE for every column - which is almost n times as expensive as updating n columns at once.
Due to PostgreSQL's MVCC model this also produces n dead tuples in the table, leading to excessive table bloat and a lot of work for VACUUM.

What's worse, it updates every row n times even if nothing changes at all.

I suggest a much more efficient approach:

  • Update every row once.
  • Only update if anything actually changes.

CREATE OR REPLACE FUNCTION f_trim_cols(IN _tbl regclass, OUT row_ct integer
                                                       , OUT col_ct integer)
  RETURNS record AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT format('
      UPDATE %s SET (%2$s) = (trim(%3$s))
      WHERE (%2$s) IS DISTINCT FROM (trim(%3$s))' -- test if anything changes
                , _tbl
                , string_agg (quote_ident(attname), ', ')
                , string_agg (quote_ident(attname), '), trim(')
                )
        , count(*)::int  -- count columns
   INTO   _sql, col_ct
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    atttypid IN ('text'::regtype, 'varchar'::regtype) -- basic string types
   AND    NOT attisdropped 
   AND    attnum > 0;

   -- RAISE NOTICE '%', _sql;  --  debug
   EXECUTE _sql;  GET DIAGNOSTICS row_ct = ROW_COUNT;
END
$func$  LANGUAGE plpgsql;

COMMENT ON FUNCTION f_trim_cols(regclass) IS 'Trim all columns of table _tbl ($1).
Return count of applicable columns (col_ct) and rows actually changed (row_ct) .
Call:
   SELECT * FROM f_trim_cols($$city$$)   --';

Produces SQL code like:

 UPDATE city SET (city, country) = (trim(city), trim(country))
 WHERE (city, country) IS DISTINCT FROM (trim(city), trim(country));

I also target text as well as varchar columns.
Everything is sanitized and safe against SQL injection.
More explanation, links and a fiddle in this related answer on SO:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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