Cursor doesn't fetch values correctly in Postgresql

Question

Here is the table that I wanna fetch info from:

CREATE TABLE public.insurance_premiums (
 contract_code text NOT NULL,
 insurance_team text NOT NULL,
 starting_date date NOT NULL,
 expiration_date date NOT NULL,
 contract_cost float8 NOT NULL,
 vehicle_contract text NOT NULL,
 customer_contract text NOT NULL,
 driver_contract text NOT NULL,
 CONSTRAINT insurance_premiums_pkey PRIMARY KEY (contract_code)
);

Here is the Cursor:

CREATE OR REPLACE FUNCTION get_info (s_d_1 DATE, s_d_2 DATE)
RETURNS TABLE (
 contract_code text,
 customer_contract text,
 driver_contract text
) AS $$
 declare
 new_contract record;
 code text;
 customer text;
 driver text;
 new_contract_cursor CURSOR (s_d_1 DATE, s_d_2 DATE) FOR
 select insurance_premiums.contract_code, insurance_premiums.customer_contract, insurance_premiums.driver_contract 
 from public.insurance_premiums 
 where starting_date >= s_d_1 and starting_date <= s_d_2;
 
 begin
 
 open new_contract_cursor(s_d_1, s_d_2);
 
 loop
 fetch new_contract_cursor into new_contract;
 exit when not found;
 code := new_contract.contract_code;
 customer := new_contract.customer_contract;
 driver := new_contract.driver_contract;
 return next;
 end loop;
 
 close new_contract_cursor;
 
 end;$$ language plpgsql;

Note: s_d_1 stands for starting_date_1 same goes for s_d_2.

Here is also the simple SELECT query:

select insurance_premiums.contract_code, insurance_premiums.customer_contract, insurance_premiums.driver_contract from public.insurance_premiums 
where starting_date >= '2021/06/01' and starting_date <= '2021/06/30';

This SELECT query fetches the correct data from the table. It simply returns 2 rows with 3 columns that are all text.

The cursor on the other hand, even though it creates a new table and correctly fetches 2 rows and 3 columns all the data is NULL instead of text.

I'm guessing that something is wrong inside the loop or in the select query inside the cursor.

I also thought about the fact that I create a new table with column names being the same from the table I fetch might have to do anything. I don't think this is it but you never know.

Answer 1

You never assigned to the variables contract_code, customer_contract and driver_contract, so of course the result columns are all NULL.

Use

get_info.contract_code := new_contract.contract_code;
get_info.customer_contract := new_contract.customer_contract;
get_info.driver_contract := new_contract.driver_contract;
RETURN NEXT;