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"
SELECT DISTINCT county FROM my_counties
select into new_table
should be replaced with the standard compliantcreate table as select...
returns void