convert 'text' fields into varchar fields of an appopriate length

gistfile1.txt

-- convert 'text' fields into varchar fields of an appopriate length (useful for exporting to shp, dbf, etc)

CREATE OR REPLACE FUNCTION set_varchar_length (schema_name text, table_name text) RETURNS VOID AS $$
DECLARE rec record;
DECLARE len int;
DECLARE qry text;
DECLARE is_first boolean;
BEGIN
  qry := 'ALTER TABLE ' || quote_ident(schema_name) || '.' || quote_ident(table_name);
  is_first := true;
  FOR rec in EXECUTE ('SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = $1 and table_name =$2 and data_type=''text'' ') USING schema_name, table_name LOOP
    EXECUTE 'SELECT 1+ max(length(' || rec.column_name || ')) FROM ' || quote_ident(schema_name) || '.' || quote_ident(table_name) INTO len;
    IF NOT is_first THEN
      qry := qry || ',';
    END IF;

    is_first :=false;
    qry := qry || ' ALTER COLUMN ' || rec.column_name || ' SET DATA TYPE varchar(' || len::text || ')';
  END LOOP;

  IF NOT is_first THEN
    EXECUTE qry;
  END IF;
END;
$$ LANGUAGE plpgsql;