Report Layout Conversion Functions

PostgreSQL functions for exporting, modifying, and re-importing report layouts stored as bytea.

1. Extract Report Layout as XML

Extracts the reportlayout (stored as bytea) for a given report ID, decodes it, and returns as XML string.

CREATE OR REPLACE FUNCTION reports.extractreportasxml(reportid_ bigint)
RETURNS text
LANGUAGE 'plpgsql'
AS $$
DECLARE
    _reportlayout bytea;
BEGIN
    SELECT reportlayout
    INTO _reportlayout
    FROM reports.reports
    WHERE intid = reportid_;
    
    IF _reportlayout IS NULL THEN
        RAISE EXCEPTION 'Report not found with ID: %', reportid_;
    END IF;
    
    RETURN convert_from(_reportlayout, 'UTF8');
END;
$$;

Usage

SELECT reports.extractreportasxml(284);

2. Update Report Layout with Modified XML

Updates the reportlayout with a new modified XML layout for the given report ID.

CREATE OR REPLACE FUNCTION reports.updatereportlayoutxml(
    reportid_ bigint,
    modified_xml_ text
)
RETURNS void
LANGUAGE 'plpgsql'
AS $$
BEGIN
    UPDATE reports.reports
    SET reportlayout = convert_to(modified_xml_, 'UTF8')
    WHERE intid = reportid_;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Report not found with ID: %', reportid_;
    END IF;
END;
$$;

Usage

SELECT reports.updatereportlayoutxml(284, '<?xml version="1.0" encoding="utf-8"?>...');

3. Extract Attribute from XML

Generic function to extract any section or attribute using XPath.

CREATE OR REPLACE FUNCTION extractfromxml(
    xml_content_ TEXT,
    xpath_expression_ TEXT
)
RETURNS TEXT AS $$
DECLARE
    _result TEXT;
BEGIN
    SELECT xpath(xpath_expression_, xmlparse(document xml_content_))::TEXT
    INTO _result;
    
    _result := regexp_replace(_result, '[{}]', '', 'g');
    RETURN _result;
END;
$$ LANGUAGE plpgsql;

Usage

SELECT extractfromxml(
    '<XtraReportsLayoutSerializer>...</XtraReportsLayoutSerializer>',
    '//ComponentStorage/Item1/@Base64'
);

4. Replace Content in XML

Replaces content within a specific section of an XML document.

CREATE OR REPLACE FUNCTION replaceinxml(
    xml_content_ TEXT,
    xpath_expression_ TEXT,
    new_value_ TEXT
)
RETURNS TEXT AS $$
DECLARE
    _updatedxml TEXT;
    _currentvalue TEXT;
BEGIN
    SELECT xpath(xpath_expression_, xmlparse(document xml_content_))::TEXT
    INTO _currentvalue;
    
    _currentvalue := regexp_replace(_currentvalue, '[{}]', '', 'g');
    _updatedxml := regexp_replace(xml_content_, _currentvalue, new_value_, 'g');
    
    RETURN _updatedxml;
END;
$$ LANGUAGE plpgsql;

Usage

SELECT replaceinxml(
    '<XtraReportsLayoutSerializer>...</XtraReportsLayoutSerializer>',
    '//ComponentStorage/Item1/@Base64',
    'NEW_BASE64_STRING'
);