An example on plpgsql functions returning a (single) record. #postgres #plpgsql

func-returning-record.sql

--
-- An example of a function returning a (single) record of (integer, integer, text)
--

CREATE OR REPLACE FUNCTION examine_change_of_status(
    IN status text, IN prev_status text,
    -- output parameters (correspond to fields of returned record)
    OUT a_count integer, OUT b_count integer, OUT answer text) 
AS $body$
BEGIN
    RAISE NOTICE 'Enter: status = %', status;
    IF status = 'A' THEN
        IF prev_status is null THEN 
            a_count := +1;
        ELSE
            a_count := 0;
        END IF;
        b_count := 0;
        answer := 'Aa'; 
    ELSIF status = 'B' THEN
        a_count := 0;
        b_count := +1;
        answer := 'Bb'; 
    ELSE
        RAISE NOTICE 'Not a valid status: %', status;
        answer := 'x';
    END IF;
    
END;
$body$ LANGUAGE plpgsql;

--
-- An example of calling the above function and expanding a returned record inside a parent SELECT
--

drop table if exists test1;
create temp table test1 (status text, prev_status text);
insert into test1 values ('A', null), ('A', 'A'), ('B', 'A'), ('X', 'A'); 


-- Note: Do not use syntax (examine_change_of_status(s, s1)).* because it results in 
--   executing the function multiple times!!
--   See https://stackoverflow.com/questions/24444008/how-can-you-expand-a-condensed-postgresql-row-into-separate-columns
--   See https://www.2ndquadrant.com/en/blog/join-lateral/ 
SELECT 
    *
FROM test1
    CROSS JOIN LATERAL examine_change_of_status(test1.status, test1.prev_status);