Supabase function for applying CRUD transactions atomically via PostgREST. It accepts a JSON array of CRUD operations (upsert, update, delete) and executes them in a transaction.

atomic_crud.sql

CREATE OR REPLACE FUNCTION "public"."atomic_crud"("operations" "jsonb") RETURNS "void"
    LANGUAGE "plpgsql"
    AS $$DECLARE
    operation jsonb;
    table_name text;
    row_id text;
    op text;
    op_data jsonb;
    column_data jsonb;
    column_name text;
    column_value text;
    insert_columns text := '';
    insert_values text := '';
    upsert_sets text := '';
    update_sets text := '';
    query text;
    comma text;
BEGIN
    -- Loop through each operation in the array
    FOR operation IN SELECT jsonb_array_elements(operations) LOOP
        -- Extract operation details with shortened field names
        table_name := operation->>'t';
        row_id := operation->>'id';
        op := operation->>'o';
        op_data := operation->'d';
        
        -- Validate input
        IF table_name IS NULL OR row_id IS NULL OR op IS NULL THEN
            RAISE EXCEPTION 'Invalid operation: t (table), id, and o (operation) are required fields';
        END IF;
        
        IF op NOT IN ('I', 'U', 'D') THEN
            RAISE EXCEPTION 'Invalid operation type: %. Must be I (upsert), U (update), or D (delete)', op;
        END IF;
        
        IF op IN ('I', 'U') AND op_data IS NULL THEN
            RAISE EXCEPTION 'd (data) is required for I (upsert) and U (update) operations';
        END IF;
        
        -- Handle deletion
        IF op = 'D' THEN
            EXECUTE format('DELETE FROM %I WHERE id = %L', table_name, row_id);
            CONTINUE;
        END IF;
        
        -- Process column data for upsert or update
        insert_columns := '';
        insert_values := '';
        upsert_sets := '';
        update_sets := '';
        comma := '';
        
        -- Add id to the upsert data
        IF op = 'I' THEN
            insert_columns := 'id';
            insert_values := quote_literal(row_id);
            upsert_sets := 'id = EXCLUDED.id';
            comma := ',';
        END IF;
                
        -- Process each column in op data
        FOR column_data IN SELECT jsonb_array_elements(op_data) LOOP
            column_name := quote_ident(column_data->>0);
            column_value := CASE
                WHEN jsonb_typeof(column_data->1) = 'null' THEN 'NULL'
                WHEN jsonb_typeof(column_data->1) = 'string' THEN quote_literal(column_data->>1)
                ELSE column_data->>1
            END;

            IF column_name IS NULL THEN
                RAISE EXCEPTION 'Column name cannot be null';
            END IF;
            
            -- Build parts for queries
            IF op = 'I' THEN
                insert_columns := insert_columns || comma || column_name;
                insert_values := insert_values || comma || column_value;
                upsert_sets := upsert_sets || comma || column_name || ' = EXCLUDED.' || column_name;
            ELSIF op = 'U' THEN
                update_sets := update_sets || comma || column_name || ' = ' || column_value;
            END IF;
            
            comma := ',';
        END LOOP;
        
        -- Execute the appropriate query
        IF op = 'I' THEN
            query := format(
                'INSERT INTO %I (%s) VALUES (%s) ON CONFLICT (id) DO UPDATE SET %s',
                table_name,
                insert_columns,
                insert_values,
                upsert_sets
            );
            EXECUTE query;
            
        -- Only execute if there are fields to update
        ELSIF op = 'U' AND update_sets <> '' THEN
            query := format(
                'UPDATE %I SET %s WHERE id = %L',
                table_name,
                update_sets,
                row_id
            );
            EXECUTE query;
        END IF;
    END LOOP;
END;$$;


ALTER FUNCTION "public"."atomic_crud"("operations" "jsonb") OWNER TO "postgres";