Crear funciones SQL en Supabase concatenando INSERT

Las funciones SQL permiten ser llamadas desde la aplicación y realizar diferentes acciones de una forma unificada, como por ejemplo el caso siguiente, donde condicionalmente se realizan dos «INSERT» y el segundo utiliza el ID del item que se ha creado en el primero. Esto permite agilizar por ejemplo, los tiempos de espera en el caso de hacer dos llamadas de forma tradicional. Edemás, esto permite escalar de forma más rápida y que la experiencia de usuario sea la adecuada.

En el siguiente código se encuentran varios valores de entrada:

• itemdata. Será un objeto JSONB
• duedate. Será una fecha (el formato timestamp de entrada es porque así está definido en la base de datos).

El código está desglosado por bloques para simplificar su lectura y comprensión:

• El bloque 1 extrae las claves y columnas del objeto itemdata, dado que solo se enviarán algunos campos. Se crea una consulta teniendo en cuenta dichos campos y si además vienen con nombres específicos (por ejemplo, en mayúsculas).
• El bloque 2 lo que hace es construir la consulta SQL usando esas claves y los valores.
• El bloque 3 ejecuta la consulta SQL y devuelve el ID generado en el bloque 2 y lo almacena en la variable «itemdata».
• El bloque 4 comprueba si viene la varianle duedate y en tal caso se añade a la tabla correspondiente usando el itemdate creado en el paso anterior.
• El bloque 5 devuelve finalmente el «itemdata» generado en el bloque 3.

Se deberá tener especial cuidado en estos aspectos:

• Definir correctamente los tipos de las variables de entrada (jsonb y timestamp en el caso anterior).
• Definir el tipo de respuesta que se tendrá de la consulta completa, en este caso es del tipo uuid.
• Declarar variables y su formato de forma adecuada que se usarán a lo largo de la consulta SQL.

El código a modo de resumen es el siguiente:

create or replace function insert_item_duedate (itemdata jsonb, duedate timestamp default null) RETURNS uuid as $$
DECLARE
    newitemid uuid;
    claves text[];
    val text[];
    columnas text;
    parametros text;
    sql text;
    salida text;
BEGIN
    
    -- 1. Obtener dinámicamente la lista de columnas a partir de las claves del JSON.
    -- Esto asegura que solo incluiremos las columnas presentes en el objeto de entrada.
    claves := ARRAY(SELECT jsonb_object_keys(itemdata));
    columnas := string_agg(quote_ident(k), ', ') FROM unnest(claves) AS k;

    SELECT columnas INTO columnas FROM jsonb_object_keys(itemdata);

    -- Si el JSON está vacío, no hay nada que insertar.
    IF columnas IS NULL THEN
        RAISE EXCEPTION 'El JSON de entrada no puede estar vacío o no es un objeto válido.';
    END IF;

    -- 2. Construir la consulta dinámica.
    -- La clave está en usar la lista de columnas dos veces:
    -- una para la cláusula INSERT y otra para la cláusula SELECT.
    sql := format(
        'INSERT INTO items (%s) SELECT %s FROM jsonb_populate_record(null::items, $1) RETURNING id',
        columnas, -- Se inserta en: (boardId, title, ...)
        columnas  -- Se selecciona: boardId, title, ...
    );

    -- 3. Ejecutar la consulta pasando el JSON original como único parámetro ($1).
    -- jsonb_populate_record se encargará de la conversión de tipos dentro de la ejecución.
    EXECUTE sql INTO newitemid USING itemdata;
        

    -- 4. Si se ha proporcionado duedate, lo metemos en la base de datos
    IF duedate IS NOT NULL THEN
        -- Insert a new record into the itemsDueDate table with the provided due_date
        INSERT INTO "itemsDueDate" ("itemId", "dueDate") VALUES (newitemid, duedate);
    END IF;

    -- 5 Devolver el ID del Item que se ha creado
    RETURN newitemid;
END;
$$ LANGUAGE plpgsql;

Como actualización, se puede añadir adicionalmente un campo de entrada que sea un array de texto:

customers text[]

A nivel de lógica, la consulta para añadir dicho array de valores a la tabla correspondiente, la variable «d» está definida del tipo uuid (d uuid) por el tipo de contenido que tendrá:

FOREACH d IN ARRAY customers
    LOOP

    sql := format(
        'INSERT INTO "itemsCompanies" ("itemId","companyId") VALUES ($1,$2);');
        EXECUTE sql USING newitemid, d;

END LOOP;