Skip to content

Instantly share code, notes, and snippets.

@Radiokot

Radiokot/atomic_crud.sql

Last active May 11, 2025 06:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Radiokot/fbc8d1a7cf283d1f476938ca573ced82 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Radiokot/fbc8d1a7cf283d1f476938ca573ced82 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Radiokot revised this gist May 11, 2025. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion atomic_crud.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -15,7 +15,6 @@ CREATE OR REPLACE FUNCTION "public"."atomic_crud"("operations" "jsonb") RETURNS
    update_sets text := '';
    query text;
    comma text;
    using_conflict_target text;
    BEGIN
    -- Loop through each operation in the array
    FOR operation IN SELECT jsonb_array_elements(operations) LOOP
  2. Radiokot revised this gist May 11, 2025. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions atomic_crud.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -47,6 +47,7 @@ BEGIN
    -- Process column data for upsert or update
    insert_columns := '';
    insert_values := '';
    upsert_sets := '';
    update_sets := '';
    comma := '';

  3. Radiokot revised this gist May 10, 2025. No changes.
  4. Radiokot created this gist May 10, 2025.
    111 changes: 111 additions & 0 deletions atomic_crud.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,111 @@
    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;
    using_conflict_target 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 := '';
    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";