title: Turning a pl/pgSQL select statement into an insert
body: <p>I'm trying to turn this select query into an insert query to insert the results into another table, but PostgreSQL is telling me no. This query works and returns a 'reports' data type:</p>
  
  <pre><code>drop function counties();
  
  create or replace function counties()
  returns table(code varchar(16), county varchar(50), count bigint) as $$
  DECLARE
      new_version_number varchar(50) := concat('gcversa00', MAX("versionnumber")) from "gcdefault"."versionhistory";
      old_version_number varchar(50) := concat('gcversa00', MAX("versionnumber"-2)) from "gcdefault"."versionhistory";
  BEGIN RETURN QUERY EXECUTE
  format(
      'with cte_current as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                   where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),
  
           cte_new     as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                   where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),
  
  
          cte_union as (select code, county from cte_current
          union all
          select code, county from cte_new)
  
      select code,county, count(*) as count
      from cte_union
      group by code, county
      Having count (*) &lt;&gt; 2', new_version_number, old_version_number
      );
  END;
  $$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>When I turn this into an insert query and call <code>select counties()</code>:</p>
  
  <pre><code>drop function counties();
  
  create or replace function counties()
  returns table(code varchar(16), county varchar(50), count bigint) as $$
  DECLARE
      new_version_number varchar(50) := concat('gcversa00', MAX("versionnumber")) from "gcdefault"."versionhistory";
          old_version_number varchar(50) := concat('gcversa00', MAX("versionnumber"-2)) from "gcdefault"."versionhistory";
  BEGIN RETURN QUERY EXECUTE
  format(
      '
      with cte_current as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                   where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),
  
           cte_new     as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                   where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),
  
  
          cte_union as (select code, county from cte_current
          union all
          select code, county from cte_new)
  
      insert into county_check (code, county, count)
      select code, county, count(*) as count from cte_union
      group by code, county
      Having count (*) &lt;&gt; 2', new_version_number, old_version_number
      );
  END;
  $$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>This is the error I get:</p>
  
  <pre><code>ERROR:  cannot open INSERT query as cursor
  CONTEXT:  PL/pgSQL function counties() line 5 at RETURN QUERY
  </code></pre>
  
  <p>How can I make this work as an insert statement similar to the way I have it laid out now (if possible)? I looked at creating some temp tables instead of using the cte's in the query, and then using a <code>select into</code> loop, but I can't find any examples this complex that I can use. </p>