Can i update a table structure and access it in a plpgsql function?

Question

I'm trying to create a function in PostgreSQL to get data from a table in a dynamic way, but if i update the structure of "my_table_2023", it will raise the error because the structure doesn't match:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying(255) does not match expected type double precision in column 6.
CONTEXT:  SQL statement "SELECT * FROM csv_congregational_ministries_2023_20240109090450;"
PL/pgSQL function dynamic_query_single_table(anyelement,integer) line 15 at RETURN QUERY

These are the functions and views used:

CREATE VIEW tables_per_year AS
SELECT DISTINCT ON (basename, yyyy) yyyy, id, name, basename
FROM (
    SELECT
        id,
        created_at,
        name,
        CAST(SPLIT_PART(name, '_', -2) AS INTEGER) AS yyyy,
        LEFT(name, POSITION('_' || CAST(SPLIT_PART(name, '_', -2) AS TEXT) IN name) - 1) AS basename
    FROM "metabase_table"
    WHERE name LIKE 'csv_%'
    ORDER BY created_at DESC
) AS tables_per_year;


CREATE OR REPLACE FUNCTION get_table(_table_name text, year int)
RETURNS text AS $$
    DECLARE
        table_selected text;
    BEGIN
        EXECUTE 'SELECT name FROM tables_per_year WHERE name ILIKE $1 AND  yyyy = $2 LIMIT 1;'
        INTO table_selected
        USING '%' || _table_name || '%', year;

        RETURN table_selected::TEXT;
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION dynamic_query_single_table(_table anyelement, _year int)
RETURNS SETOF anyelement AS $$
    DECLARE
        table_partial_name text;
        table_selected text;
    BEGIN
        table_partial_name := pg_typeof(_table)::text;
        table_selected := get_table(table_partial_name, _year);
        EXECUTE FORMAT('DROP TABLE IF EXISTS %s; CREATE TABLE %s AS SELECT * FROM %s;', table_partial_name, table_partial_name, table_selected);

        RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s;', table_selected);
    END;
$$ LANGUAGE plpgsql;

Query:

SELECT * FROM dynamic_query_single_table(NULL::my_table, 2023);

I've tried to run the DROP and CREATE in a query and call dynamic_query_single_table in two different queries and it works fine.

I think it doesn't commit the queries with DROP TABLE and CREATE TABLE in dynamic_query_single_table because it's a single transaction. Thanks.

Answer 1

I solved using a TRIGGER and creating a function to create tables, this is the full code:

CREATE OR REPLACE FUNCTION create_tables(year int)
RETURNS void AS $$
    DECLARE
        table_name_1 text := 'congregational_ministries';
        table_name_2 text := 'individual_ministries';
        table_selected_1 text;
        table_selected_2 text;
    BEGIN
        table_selected_1 := get_table(table_name_1, year);
        table_selected_2 := get_table(table_name_2, year);
        
        IF table_selected_1 IS NOT NULL THEN
            EXECUTE FORMAT('DROP TABLE IF EXISTS %s; CREATE TABLE %s AS SELECT * FROM %s;', table_name_1, table_name_1, table_selected_1);
        END IF;
        
        IF table_selected_2 IS NOT NULL THEN
            EXECUTE FORMAT('DROP TABLE IF EXISTS %s; CREATE TABLE %s AS SELECT * FROM %s;', table_name_2, table_name_2, table_selected_2);
        END IF;
        
        RAISE NOTICE 'Tables created.';
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION create_tables_trigger_function()
RETURNS TRIGGER AS $$
    DECLARE
        _year integer;
    BEGIN
        EXECUTE FORMAT('SELECT yyyy FROM tables_per_year WHERE name LIKE ''%s'' LIMIT 1;', NEW.name) INTO _year;
        
        PERFORM create_tables(_year);

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER create_tables_trigger
AFTER INSERT ON metabase_table
FOR EACH ROW
WHEN (
    NEW.name LIKE 'csv_congregational_ministries_%' OR NEW.name LIKE 'csv_individual_ministries_%'
)
EXECUTE FUNCTION create_tables_trigger_function();


CREATE OR REPLACE FUNCTION dynamic_query_single_table(_table anyelement, _year int)
RETURNS SETOF anyelement AS $$
    DECLARE
        table_partial_name text;
        table_selected text;
    BEGIN
        table_partial_name := pg_typeof(_table)::text;
        table_selected := get_table(table_partial_name, _year);
        
        RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s;', table_selected);
    END;
$$ LANGUAGE plpgsql;

Thanks for your advices.