load_needed_things.sql

-- Must only be in localhost

DO $$
BEGIN
    IF current_database() <> 'myDatabaseName' THEN
        RAISE EXCEPTION 'This script can only be run on myDatabaseName.';
    END IF;

    IF inet_server_addr() IS NOT NULL AND inet_server_addr() NOT IN ('127.0.0.1', '::1') THEN
        RAISE EXCEPTION 'This script can only be run on localhost.';
    END IF;
END $$;


DROP TABLE IF EXISTS Bad_Apple_Frame_Table;
CREATE TABLE Bad_Apple_Frame_Table (
    Frame_Id SERIAL PRIMARY KEY,
    Frame_Data JSONB
);


DROP FUNCTION IF EXISTS expand_bad_apple_frame;
CREATE OR REPLACE FUNCTION expand_bad_apple_frame(frame_i INT)
RETURNS TABLE (
    -- Need to hardcode colNames
    col0 CHAR(17), col1 CHAR(17), col2 CHAR(17), col3 CHAR(17), col4 CHAR(17),
    col5 CHAR(17), col6 CHAR(17), col7 CHAR(17), col8 CHAR(17), col9 CHAR(17),
    col10 CHAR(17), col11 CHAR(17), col12 CHAR(17), col13 CHAR(17), col14 CHAR(17),
    col15 CHAR(17), col16 CHAR(17), col17 CHAR(17), col18 CHAR(17), col19 CHAR(17),
    col20 CHAR(17), col21 CHAR(17), col22 CHAR(17), col23 CHAR(17)
) AS $$
DECLARE
    Frame JSONB;
    ColNames TEXT;
    Frame_Num VARCHAR(10);
    Sql TEXT;
BEGIN
    -- The ID starts at 1
    Frame := (SELECT Frame_Data FROM Bad_Apple_Frame_Table WHERE Frame_Id = frame_i + 1);

    -- You can dynamically create cols in this case because it is a string to be executed as SQL
    SELECT string_agg(
        format('col%s CHAR(17) PATH ''$.col%s''', colNum, colNum),
        ', '
    )
    INTO ColNames
    FROM generate_series(0, 23) AS g(colNum);

    Frame_Num := format('frame%s', frame_i);

    -- Format the string first and execute it
    Sql := format('SELECT * FROM json_table(%L::jsonb->''%s'', ''$[*]'' COLUMNS (%s))', Frame, Frame_Num, ColNames);
    RETURN QUERY EXECUTE Sql;
END;
$$ LANGUAGE plpgsql;