title: How to declare a function with a select statement to reset all auto_increment values to max id of the tables in postgresql
body: <p>I have a Postgres database with many tables. I want to declare a function that reset all auto_increment values (next_value) of all the tables to max(id) of the table. For example, if <strong>max(id)</strong> of <strong>sample_table</strong> is <strong>22</strong>:</p>
  <pre><code>postgres=# SELECT MAX(id) + 1 FROM sample_table;
   ?column? 
  ----------
         22
  (1 row)
  </code></pre>
  <p>While <strong>next_value</strong> of the table is <strong>7</strong>:</p>
  <pre><code>postgres=# SELECT nextval('&quot;public&quot;.&quot;sentry_project_id_seq&quot;'::regclass);
   nextval 
  ---------
         7
  (1 row)
  </code></pre>
  <p>I want to set the <strong>next_value</strong> of all the tables to their <strong>max(id)</strong> by:</p>
  <pre><code>SELECT setval('auth_user_id_seq', (SELECT MAX(id) from &quot;auth_user&quot;));
  </code></pre>
  <p>To do this, I declare a function called <strong>reset_auto_increment</strong> by:</p>
  <pre><code>CREATE OR REPLACE FUNCTION reset_auto_increment(username IN VARCHAR) RETURNS void AS $$
  DECLARE
          statements CURSOR FOR
              SELECT tablename FROM pg_tables
              WHERE tableowner = username AND schemaname = 'public';
      BEGIN
          FOR stmt IN statements LOOP
              EXECUTE 'SELECT setval(' || concat(stmt.tablename, '_id_seq') || ' , (SELECT MAX(id) from ' || quote_ident(stmt.tablename) || '));';
          END LOOP;
      END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  <p>But the function declared by the statement above has error below:</p>
  <pre><code>postgres=# select reset_auto_increment('postgres');
  **ERROR:  column &quot;_id_seq&quot; does not exist**
  LINE 1: SELECT 'SELECT setval(' || concat(stmt.tablename, &quot;_id_seq&quot;)...
                                                            ^
  QUERY:  SELECT 'SELECT setval(' || concat(stmt.tablename, &quot;_id_seq&quot;) || ' , (SELECT MAX(id) from ' || quote_ident(stmt.tablename) || '));'
  CONTEXT:  PL/pgSQL function reset_auto_increment(character varying) line 8 at EXECUTE
  </code></pre>
  <p>How to correct this function?</p>