plpgsql returning a composite type with multiple rows. Using a select statement as an IN parameter for a function

This is a 2 part question. I am currently in the process of converting some oracle functions into postgres.

I converted the below into postgres, but the problem is that it only outputs one row:

CREATE TYPE m_t_stts_ot AS (
id INTEGER,
name TEXT,
stts_nm TEXT,
stts_ds TEXT,
f_s_f CHARACTER(1),
t_ts TIMESTAMP WITHOUT TIME ZONE,
e_id INTEGER,
s_efctv_dt TIMESTAMP WITHOUT TIME ZONE,
m_s_id INTEGER,
m_d_o_fl CHARACTER(1)
);


CREATE OR REPLACE FUNCTION get_s_fn(IN in_cur integer[], IN in_ts TIMESTAMP WITHOUT TIME ZONE)
RETURNS SETOF m_t_stts_ot 
AS
$BODY$
DECLARE
    o_rec s_ot;
    v_id s_id%TYPE;
    rec record;

BEGIN
FOR rec IN       
        SELECT sr.*,
             mtsv.id,
             mtsv.name,
             mtsv.status ,
             mtsv.stts_ds ,
             mtsv.ts,
             coalesce(mtsv.ts,sr.crtd_dt),
             mtsv.e_dt,
             mtsv.ovrrd_fl
        FROM RAW sr
        LEFT JOIN (select * from get_task_stts_fn(ARRAY(SELECT distinct id
                                                        FROM prpty
                                                       WHERE value_nb in (select * from unnest(in_cur))
                                                         AND prpty_id in( 20017, 21021, 22017)), in_ts))mtsv
          ON ( sr.event_id = mtsv.event_id AND
               mtsv.f_stts_fl = 'Y'    AND
               mtsv.task_ts <= in_ts )
       WHERE sr.id in(select * from unnest(in_cur))        
         AND sr.crtd_dt <= cast(in_ts as date)
         AND ( mtsv.id is not null OR
               sr.event_id is null
             )
       ORDER BY ( case
                  when mtsv.id is not null then
                       task_ts
                  else
                       sr.crtd_dt
                  end ) asc


    LOOP
            IF ((o_rec).STTS_CD = 'A')
            THEN
                IF (((o_rec).c_cd IN ('E', 'R')) AND (rec.chng_ts > (o_rec).M_DT::DATE - INTERVAL '13 month') AND ((o_rec).ROLL_FL = 'Y')) THEN
                    o_rec.M_DT := (o_rec).M_DT::DATE + INTERVAL '12 month';

                    IF (o_rec).c_cd = 'R' THEN
                        o_rec.C_END_DT := (o_rec).M_DT::DATE - INTERVAL '12 month';
                    END IF;
                END IF;

                IF (((o_rec).c_cd NOT IN ('E', 'R')) AND (rec.chng_ts > (o_rec).M_DT::DATE - INTERVAL '7 month') AND ((o_rec).ROLL_FL = 'Y')) THEN
                    o_rec.M_DT :=(o_rec).M_DT::DATE + INTERVAL '12 month';
                END IF;

                IF (((o_rec).c_cd = 'E') AND (rec.chng_ts > (o_rec).M_DT::DATE - INTERVAL '12 month') AND ((o_rec).ROLL_FL = 'N')) THEN
                    o_rec.c_cd := 'DBT';
                END IF;
            END IF;

            IF ((rec.c_type_cd LIKE 'SUB%' AND rec.c_type_cd NOT LIKE '%-AMNDT%') OR (rec.event_id IS NULL AND rec.c_type_cd IS NULL)) THEN
                o_rec.s_id := rec.s_id;
                o_rec.c_nb := rec.c_nb;
                o_rec.f_nm := rec.f_nm;
                o_rec.f_c_nm := rec.f_c_nm;
                o_rec.f_c_e_tx := rec.f_c_e_tx;
                o_rec.f_c_t_t := rec.f_c_t_t;
                o_rec.f_a_t := rec.f_a_t;
                o_rec.f_c_nm := rec.f_c_nm;
                o_rec.f_s_c := rec.f_s_c;
                o_rec.f_z_t := rec.f_z_t;
                o_rec.g_s_t := rec.g_s_t;
                o_rec.p_a := rec.p_a;
                o_rec.a_b_a := rec.a_b_a;
                o_rec.e_dt := rec.e_dt;
                o_rec.M_DT := rec.M_DT;
                o_rec.t_nb := rec.t_nb;
                o_rec.i_t_cd := rec.i_t_cd;
                o_rec.i_r := rec.i_r;
                o_rec.i_r_tx := rec.i_r_tx;
                o_rec.i_f_tx := rec.i_f_tx;
                o_rec.c_cd := rec.c_cd;
                o_rec.s_t_cd := rec.s_t_cd;
                o_rec.s_s_cd := rec.s_s_cd;
                o_rec.ROLL_FL := rec.ROLL_FL;
                o_rec.C_END_DT := rec.C_END_DT;
                o_rec.f_m_s_l_fl := rec.f_m_s_l_fl;
                o_rec.p_p_p_fl := rec.p_p_p_fl;
                o_rec.a_m_o_e_fl := rec.a_m_o_e_fl;
                o_rec.e_tx := rec.e_tx;
                o_rec.e_l_fl := rec.e_l_fl;
                o_rec.r_u_u_fl := rec.r_u_u_fl;
                o_rec.r_p_fl := rec.r_p_fl;
                o_rec.n_o_n := rec.n_o_n;
                o_rec.n_a := rec.n_a;
                o_rec.n_s_d := rec.n_s_d;
                o_rec.n_f_t := rec.n_f_t;
                o_rec.a_m_o_e_fl := rec.a_m_o_e_fl;
                o_rec.e_tx := rec.e_tx;
                o_rec.s_a_i_a_fl := rec.s_a_i_a_fl;
                o_rec.l_r_t_a_m_fl := rec.l_r_t_a_m_fl;
                o_rec.d_o_fl := rec.d_o_fl;
                o_rec.o_c_nb := rec.o_c_nb;
                o_rec.d_cmt_tx := rec.d_cmt_tx;
                o_rec.f_cmt_tx := rec.f_cmt_tx;

                IF rec.name LIKE 'N L A' THEN
                    IF rec.stts LIKE 'S T F A' THEN
                        o_rec.s_s_cd := 'Approved';
                        o_rec.e_dt := COALESCE(rec.mrdt_sbl_efctv_dt::date, rec.task_ts::date);
                        o_rec.M_DT := (o_rec).e_dt + ((o_rec).t_nb::NUMERIC || ' days')::INTERVAL;
                        o_rec.d_o_fl := rec.m_d_o_fl;
                        IF (o_rec).c_cd = 'R' THEN
                            o_rec.C_END_DT := (o_rec).M_DT::DATE - INTERVAL '12 month';
                        END IF;
                    ELSIF rec.stts LIKE 'D' THEN
                        o_rec.s_s_cd := 'D';
                    ELSIF rec.stts LIKE 'W' THEN
                        o_rec.s_s_cd := 'W';
                    ELSIF rec.stts LIKE 'S C' THEN
                        o_rec.s_s_cd := 'C';
                    ELSE
                        o_rec.s_s_cd := 'S';
                    END IF;
                END IF;
            ELSIF ((rec.event_id IS NOT NULL AND rec.c_type_cd = 'S-R-A' AND rec.lfcyc_stts_ds = 'A') OR (rec.event_id IS NULL AND rec.c_type_cd = 'S-R-A'))
            THEN
                IF (rec.M_DT IS NOT NULL) THEN
                    o_rec.M_DT := rec.M_DT;
                     o_rec.t_nb :=  extract(epoch from age((o_rec).M_DT, (o_rec).e_dt))/3600;

                    IF (rec.ROLL_FL IS NOT NULL) THEN
                        o_rec.ROLL_FL := rec.ROLL_FL;
                    END IF;

                    IF (o_rec).c_cd = 'R' THEN
                        o_rec.C_END_DT := (o_rec).M_DT::DATE - INTERVAL '12 month';
                    END IF;
                END IF;

                IF (rec.i_t_cd IS NOT NULL) THEN
                    o_rec.i_t_cd := rec.i_t_cd;
                    o_rec.i_r := rec.i_r;
                    o_rec.i_r_tx := rec.i_r_tx;
                    o_rec.i_f_tx := rec.i_f_tx;
                END IF;
            ELSIF rec.event_id IS NULL
            THEN
                NULL;
            END IF;


        IF (o_rec).s_id IS NOT NULL
        THEN
            IF ((o_rec).s_s_cd = 'A')
            THEN
                IF (((o_rec).c_cd IN ('E', 'R')) AND (LEAST(current_date, (o_rec).M_DT::date) > (o_rec).M_DT::DATE - INTERVAL '13 month') AND ((o_rec).ROLL_FL = 'Y')) THEN
                    o_rec.M_DT := (o_rec).M_DT::DATE + INTERVAL '12 month';

                    IF (o_rec).c_cd = 'R' THEN
                        o_rec.C_END_DT := (o_rec).M_DT::DATE - INTERVAL '12 month';
                    END IF;
                END IF;

                IF (((o_rec).c_cd NOT IN ('E', 'R')) AND (LEAST(current_date, (o_rec).M_DT::date) > (o_rec).M_DT::DATE - INTERVAL '7 month') AND ((o_rec).ROLL_FL = 'Y')) THEN
                    o_rec.M_DT := (o_rec).M_DT::DATE + INTERVAL '12 month';
                END IF;

                IF (((o_rec).c_cd = 'E') AND (LEAST(current_date, (o_rec).M_DT::date) > (o_rec).M_DT::DATE - INTERVAL '12 month') AND ((o_rec).ROLL_FL = 'N')) THEN
                    o_rec.c_cd := 'DBT';
                END IF;
            END IF;

        END IF;
    END LOOP;        
    RETURN next o_rec;
END;
$BODY$
LANGUAGE  plpgsql;

I then call the function using:

select * from get_s_fn(ARRAY(select v_nb::integer from p where p_s_d_p_id in( 20017, 21021, 22017)), now()::timestamp);

It executes fine, but only returns 1 row. I want it to be able to return multiple rows. I have read somewhere that composite types only take the first row and then discard the rest, is there some way around this? I am looking for an answer that doesn't require me to change too much, I have a lot of these functions that I need to convert and I want to change as little as possible.

For the next part, I am trying to use a select statement with multiple rows as an IN parameter for either a type or cursor. Here is the function that I converted(I haven't tested if it actually runs because it won't accept multiple rows):

create type get_a_fn_type_in as(
s integer,
efctv_dt timestamp without time zone,
p_a float,
i_f_t text,
m_e_dt timestamp without time zone
);

CREATE OR REPLACE FUNCTION get_a_fn(in_cur get_a_fn_in[], IN in_ts TIMESTAMP WITHOUT TIME ZONE)
RETURNS SETOF s_p_n
AS
$BODY$
DECLARE
    o_rec s_p_o;
    v_s_id integer;
    v_i_f_t text;
    v_efctv_dt timestamp without time zone;
    v_p_a integer;
    v_m_e_dt timestamp without time zone;
    v_m_dt timestamp without time zone;
    v_i_r S_R.i_r%TYPE;
    v_i_t_cd text;
    v_a_a integer;
    v_a_d timestamp without time zone;
    rec record;
BEGIN
    LOOP
        select * from unnest(in_cur) INTO v_s_id, v_efctv_dt, v_p_a, v_i_f_t, v_m_e_dt;
        EXIT WHEN (NOT FOUND);


            SELECT
                MAX(p_dt)
                INTO STRICT v_a_d
                FROM s_p_r
                WHERE s_id = v_s_id AND pymnt_type_cd = 'I';

        v_a_d := (CASE
            WHEN v_a_d IS NULL THEN v_efctv_dt
            ELSE v_a_d
        END)::TIMESTAMP WITHOUT TIME ZONE;
        v_i_f_t := UPPER(SUBSTR(TRIM(v_i_f_t), 1, 1));

            v_m_dt := NULL;
            v_i_r := NULL;
            v_a_a := NULL;
            v_a_d := (CASE
                WHEN v_i_f_t = 'M' THEN v_a_d + INTERVAL '1 month'
                WHEN v_i_f_t = 'Q' THEN v_a_d + INTERVAL '3 months'
                WHEN v_i_f_t = 'S' THEN v_a_d + INTERVAL '6 months'
                WHEN v_i_f_t = 'A' THEN v_a_d + INTERVAL '12 months'
                WHEN v_i_f_t = 'D' THEN v_a_d + INTERVAL '1 days'
                ELSE NULL
            END)::TIMESTAMP WITHOUT TIME ZONE;

            BEGIN
              SELECT s_fn.mtrty_dt,
                                   s_fn.intrs_rt,
                                   s_fn.intrs_type_cd
                              INTO v_m_dt,
                                   v_i_r ,
                                   v_i_t_cd
                              FROM
                              (select * from get_s_fn((v_s_id), ci_ts))s_fn; 
                BEGIN
                END;
                EXCEPTION
                    WHEN others THEN
                        NULL;
            END;

            IF ((v_a_d <= v_m_dt - INTERVAL '12 months') AND (v_m_e_dt IS NULL OR v_a_d < v_m_e_dt) AND (v_a_d <= in_ts) AND (v_p_a > 0) AND (v_i_t_cd = 'F'))

            THEN
                v_a_a := ((v_p_a * v_i_r) /
                CASE
                    WHEN v_i_f_t = 'M' THEN 12
                    WHEN v_i_f_t = 'Q' THEN 4
                    WHEN v_i_f_t = 'S' THEN 2
                    WHEN v_i_f_t = 'A' THEN 1
                    WHEN v_i_f_t = 'D' THEN 365

                    ELSE NULL
                END)::NUMERIC;
                v_a_a := ROUND(v_a_a, 2);
            ELSE
                EXIT;

            END IF;


            IF (v_a_a > 0) THEN
                RETURN NEXT ARRAY[ROW (v_s_id, 'I', v_a_d, v_a_a, 'Accrual', v_a_d)::s_p_ot];
            END IF;
    END LOOP;
END;
$BODY$
LANGUAGE  plpgsql;

I am calling it using

select * from get_a_fn(ARRAY[(select row(id::integer, efctv_dt::timestamp, p_a::float, i_f_tx::text, m_dt::timestamp) from s_raw)]::get_a_fn_type_in[], current_timestamp::timestamp)

And I get the error ERROR: cannot cast type record to get_a_fn_type_in. So similar issue to above, except I want to use multiple rows for the in.

For both of these cases I looked into using cursors but I am not too familiar with them. Also, for the examples of the functions that used the cursors they all used the cursor with a select statement within the actual function, whereas I want to pass the cursor in as a parameter. Again, I want to change as little as possible because there are many functions that I have to convert that follow a similar pattern. I have read somewhere about creating a temporary table to store the values but that is not something I can do unless it can somehow be done within the function. The function needs to work from a simple select * from function(a, b);

728x90

2 Answers plpgsql returning a composite type with multiple rows. Using a select statement as an IN parameter for a function

Your procedure is pretty long and bad readable - and the issue is probably related to bad structure. The row is produced by RETURN NEXT command. When I read first example, then this command is executed only once time, because it is out of cycle's body. See the end of your function

    END LOOP;        
    RETURN next o_rec;
END;
$BODY$
LANGUAGE  plpgsql;

Some notes. Using useless parentheses is bad and readability of code is less.

   IF (o_rec).s_id IS NOT NULL
   THEN
        IF ((o_rec).s_s_cd = 'A')
        THEN

Just write

   IF o_rec.s_id IS NOT NULL
   THEN
        IF o_rec.s_s_cd = 'A'
        THEN

Second example - the lines

 LOOP
        select * from unnest(in_cur) INTO v_s_id, v_efctv_dt, v_p_a, v_i_f_t, v_m_e_dt;
        EXIT WHEN (NOT FOUND);

has not any sense. If you would to iterate over a array, use a FOREACH IN ARRAY statement. The error message "cannot cast type record to get_a_fn_type_in" - probably you should to cast to get_a_fn_type_in first, and then build a array. The cast to get_a_fn_type_in[] is out of PostgreSQL possibilities (or maybe you have wrong number of columns, types).

The query select * from get_a_fn(ARRAY[(select row(id::integer, efctv_dt::timestamp, p_a::float, i_f_tx::text, m_dt::timestamp) from s_raw)]::get_a_fn_type_in[], current_timestamp::timestamp)

I created function fx(get_a_fn_type_in[]) and table foo with necessary columns:

postgres=# select fx(ARRAY[(select row(id::integer, efctv_dt::timestamp, p_a::float, i_f_tx::text, m_dt::timestamp) from foo)]::get_a_fn_type_in[]);
ERROR:  cannot cast type record to get_a_fn_type_in
LINE 1: select fx(ARRAY[(select row(id::integer, efctv_dt::timestamp...
                    ^

So I moved casting to nested select, and it is ok now:

postgres=# select fx(ARRAY[(select row(id, efctv_dt, p_a, i_f_tx, m_dt)::get_a_fn_type_in from foo)]);
┌────┐
│ fx │
╞════╡
│    │
└────┘
(1 row)

4 months ago

What I ended up doing was turning the input in a String and having my function take in a string then execute it.

so:

select * from function_ex('select 1', current_timestamp::timestamp);

then inside the function i did

execute in_cur

4 months ago