Create multiple output tables from one query

Question

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"

• McNets: Maybe it should be SELECT DISTINCT county FROM my_counties
• user1822: Unrelated, but: The non-standard select into new_table should be replaced with the standard compliant create table as select...
• user1822: But why do you want to de-normalize something that sounds like a properly normalized table? Why do you "require" this? What is the problem you are trying to solve? Also: your function does not return anything, so you should define it as returns void
• caisc98: I will be supplying each county table to each individual county organisation. What do you mean by "returns void"? Thanks

Answer 1

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.