title: How to create a PostgreSQL function that I can call from DBeaver?
body: <p>Here is the sample date:</p>
  
  <pre><code>CREATE TABLE #logins (
      username text not null,
      logged_at timestamp not null);  
  insert into #logins (username, logged_at) values
      ('a','2019-01-01'),('b','2019-01-01'),('c','2019-01-01'),('d','2019-01-01'),('e','2019-01-01'),        
      ('a','2019-02-01'),('b','2019-02-01'),('c','2019-02-01'),('f','2019-02-01'),('g','2019-02-01'),
      ('h','2019-02-01'),('i','2019-02-01'),('j','2019-02-01'),('a','2019-03-01'),('b','2019-03-01'),
      ('f','2019-03-01'),('h','2019-03-01'),('g','2019-03-01'),('k','2019-03-01'),('l','2019-03-01'),
      ('m','2019-03-01'),('n','2019-03-01'),('o','2019-03-01'),('a','2019-04-01'),('f','2019-04-01'),   
      ('g','2019-04-01'),('k','2019-04-01'),('l','2019-04-01')`
  </code></pre>
  
  <p>What I normally do </p>
  
  <pre><code>drop table if exists #a;
  create table #a as 
  select username, min(logged_at) as date from #logins --Please note that there is **MIN()** here 
  group by 1;
  
  
  alter table #a 
  add m_1 varchar;
  update #a
  set m_1 = (select username from #logins 
              where add_months(#a.date,1) = #logins.logged_at and #logins.username = #a.username);
  
  alter table #a 
  add m_2 varchar;
  update #a
  set m_2 = (select username from #logins 
              where add_months(#a.date,2) = #logins.logged_at and #logins.username = #a.username);
  
  alter table #a 
  add m_3 varchar;
  update #a
  set m_3 = (select username from #logins 
              where add_months(#a.date,1) = #logins.logged_at and #logins.username = #a.username);
  
  select to_date(date,'yyyy-mm') as date, count(username) as num_acc,
                                      count(m_1) as m_1,
                                      count(m_2) as m_2,
                                      count(m_3) as m_3
  from #a
  group by 1
  order by 1
  </code></pre>
  
  <p>The expected result:</p>
  
  <pre><code>             num_acc   m_1     m_2     m_3
  2019-01-01      5       3       2       3
  2019-02-01      5       3       2       3
  2019-03-01      5       2       0       2
  </code></pre>
  
  <p>From this point I will download the data and visualize it in cohort</p>
  
  <p>The point is that I want to create a function for convenient. I am working on Dbeaver using PostgreSQL for your information.
  In this function, we only need to input a table with ID and Date then it would automate the process. </p>
  
  <p>This is my try so far: </p>
  
  <pre><code> CREATE OR REPLACE FUNCTION test(timestamp,varchar(255)) 
      RETURNS int 
  declare
      counter integer :=1
  stable 
  AS $$
  
     LOOP 
        EXIT WHEN counter = 6 ; 
        counter := counter + 1 ; 
        alter table #a
        add counter varchar;
       update #a
       counter = select user_name from #logins 
                  where add_month(#logins.logged_at,counter) = #a.first_login 
                  #a.first_login and #logins.username = #a.username
     END LOOP 
  
  $$ LANGUAGE sql;
  </code></pre>
  
  <p>This is embarrassing as <code>function</code> in SQL is quite difficult. This is the best I could do.</p>
  
  <p>(p/s: please sympathy that <code>LANGUAGE plpythonu</code> can not be used. Our only option is <code>sql</code>)</p>