0

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"

4
  • 1
    Maybe it should be SELECT DISTINCT county FROM my_counties
    – McNets
    Mar 27, 2018 at 13:19
  • 2
    Unrelated, but: The non-standard select into new_table should be replaced with the standard compliant create table as select...
    – user1822
    Mar 27, 2018 at 13:31
  • 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
    – user1822
    Mar 27, 2018 at 13:32
  • I will be supplying each county table to each individual county organisation. What do you mean by “returns void”? Thanks
    – caisc98
    Mar 27, 2018 at 13:54

1 Answer 1

0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.