Question 322319 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    To be able to handle the issue that you are currently experiencing i would suggest you can use a combination of OUT parameters and EXECUTE statement to dynamically return the result.The following query below will create a function that has an OUT parameter called result_table that will hold the result set.

    The EXECUTE statement is used to dynamically fetch the result set from the dynamically created table. The query is as follows:

    CREATE OR REPLACE FUNCTION dynamic_query_single_table(_table anyelement, _year int, OUT result_table 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);

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

    After creating the following query you can declare the above function as follows:

    DO $$ 
DECLARE 
    result_set my_table;
BEGIN 
    PERFORM dynamic_query_single_table(NULL::my_table, 2023, OUT result_set);
END $$;

    The above function will help you handle the dynamic structure of the table and return the result set without having issues with the structure mismatch.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search