Create multiple output tables from one query I'm an inexperienced Postgres user and need help if someone can suggest it.

I require output tables of column 'county' so all counties in the table have their own table. I need independent tables for each to use separately. My source table is 'my_counties' and 'county' contains the variable county names I wish to separate.

CREATE OR REPLACE FUNCTION myfunction()
RETURNS SETOF my_counties AS
$BODY$
DECLARE
    county my_counties%ROWTYPE;
BEGIN

    FOR row in SELECT county DISTINCT my_counties LOOP
        EXECUTE 'SELECT * INTO myschema.test_' || row.county || ' from myschema.source WHERE source.county = ' || row.county;
    END LOOP;

RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE


The error I return is 


  ERROR:  syntax error at or near "DISTINCT"
I'm an inexperienced Postgres user and need help if someone can suggest it.


A few things, this is a bad idea, you probably want Table Partitioning because essentially you're creating partitions of another table.

If you don't want to partition a table, you probably want a MATERIALIZED VIEW which sits as a table on the disk, but it can be refreshed from the query which is was created from with a simple command REFRESH MATERIALIZED VIEW.

What I think you want is something like this,

SELECT FORMAT ($$
  CREATE MATERIALIZED VIEW %I.%I AS
    SELECT *
    FROM my_countries
    WHERE country = %L;
  $$,
  'myschema',
  'test_' || country,
  country
)
FROM (
  SELECT DISTINCT county
  FROM my_counties
) AS t(country);


Run that in psql and then run \gexec, or loop through that result set and run EXECUTE in plpgsql.