sql-script.sql

DO $$ 
DECLARE
   partnerCount INTEGER;
   partnerDescription VARCHAR(100);
   partnerId INTEGER;
BEGIN 
    SELECT COUNT(*) INTO partnerCount 
    FROM "integrationPartners" 
    WHERE id = (
        SELECT "partnerId" 
        FROM "customerSources" 
        WHERE "auth0ClientId" = 'RVxx43NCiIGkG5hu0io3kXe4r4eXCycQ'
    );
    RAISE NOTICE '%', partnerCount;
    IF partnerCount = 0 THEN
        SELECT "description" INTO partnerDescription
        FROM "customerSources"
        WHERE "auth0ClientId" = 'RVxx43NCiIGkG5hu0io3kXe4r4eXCycQ';
        RAISE NOTICE '%', partnerDescription;
        INSERT INTO "public"."integrationPartners" ( "createdAt", "description", "updatedAt", "value") 
        VALUES ( now(), partnerDescription, now(), UPPER(partnerDescription));
        SELECT id into partnerId 
        FROM "integrationPartners"
        WHERE value = UPPER(partnerDescription);
        UPDATE "customerSources"
        SET "partnerId" = partnerId
        WHERE "auth0ClientId" = 'RVxx43NCiIGkG5hu0io3kXe4r4eXCycQ';
    END IF;
END $$;


// with loops 

 DO $$
      declare
      customersource record;
      BEGIN
        FOR customersource IN (SELECT "cs"."id" AS customer_source_id FROM "customerSources" cs)
          LOOP
            INSERT INTO "customerSourceResponseTypesLink" ( "customerSourceId", "responseTypeId", "default","createdAt","updatedAt")
            VALUES (customersource.customer_source_id, 1, true, NOW(), NOW());
          END LOOP;
      END; 
      $$