13 Trigger Function para Almacenar Pagina Web Publica

[francomac]

• haga pull, cree la nueva table normalmente como las otras.

• corra estos dos scripts en su base de datos. con el Query Tool

CREATE OR REPLACE FUNCTION update_public_website()
RETURNS TRIGGER
AS $$
DECLARE
    payload JSON;
    response JSON;
    current_timestamp TIMESTAMP;
BEGIN

    SELECT NOW() INTO current_timestamp;

    payload := json_build_object(
        'menu', (
            SELECT json_agg(json_build_object(
                'menuName', md."menuName",
                'slug', md.slug,
                'iconName', md."iconName",
                'order', md."order"
            ))
            FROM "MenuDetail" md
            JOIN "MenuHeader" mh ON md."menuHeaderId" = mh.id
            WHERE mh."websiteId" = NEW."websiteId"
        ),
        'pages', (
            SELECT json_agg(json_build_object(
                'id', p.id,
                'sections', (
                    SELECT json_agg(json_build_object(
                        'id', pt.id,
                        'element', pt.sections
                    ))
                    FROM "PageTemplate" pt
                    WHERE pt."id" = p."templateId"
                )
            ))
            FROM "Page" p
            WHERE p."websiteId" = NEW."websiteId"
        )
    );

    -- Insertar el nuevo registro en PublicWebsite
    INSERT INTO "PublicWebsite" (
        "websiteId", 
        content, 
        "isPublic", 
        "isActive", 
        "createdAt", 
        "updatedAt"
    )
    VALUES (
        NEW."websiteId", 
        payload, 
        NEW."isPublic", 
        NEW."isActive",  
        current_timestamp, 
        current_timestamp
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_public_website
AFTER INSERT OR UPDATE ON "PublishHistory"
FOR EACH ROW
EXECUTE FUNCTION update_public_website();