Question 356422 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Trouble Returning The Count of Days Past Due

I am trying to build a function to return the count of days past due where the customer was less than 3 days late. Using the dvdrental database provided by PostgreSQL. It seems as though it’s returning 6 for every row, likely the count of the first customer it comes across, or at least that’s my guess.

Here’s what I’m trying and what I need help on:

Example data:

Customer_ID days_past_due
1 2
1 2
1 1

Expected result (count of days past due less than 3 found in file returned on each row of that customer_id):

Customer_ID days_past_due
1 3
1 3
1 3 
create or replace function pd_lt3(customer_id varchar(45),days_past_due smallint)
returns smallint
language plpgsql
as $$
declare pd_lt3 smallint;
begin
    
    select total from(
    select customer_id,count(*) as total from past_due_detail where days_past_due < 3 group by customer_id
        )
    into pd_lt3;
    return pd_lt3;
end;
$$;

More Background (all of the below seems to work as intended):

Table I built for each past due occurrence:

CREATE TABLE past_due_detail(   
    customer_id integer not null,
    first_name varchar not null,
    last_name varchar not null,
    rental_id integer not null,
    rental_date timestamp not null,
    return_date timestamp,
    title varchar not null,
    days_past_due smallint,
    primary key (rental_id),
    foreign key (customer_id) references customer(customer_id)
);

Function I built for populating days past due field in table:

CREATE OR REPLACE FUNCTION public.days_past_due(
    rental_date timestamp without time zone,
    return_date timestamp without time zone,
    rental_duration smallint,
    last_update timestamp without time zone)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare days_past_due int;
begin
    select ((cast(return_date as date)) - (cast(rental_date as date)) - rental_duration) where ((cast(return_date as date)) - (cast(rental_date as date)) - rental_duration) > 0 and return_date is not null
        into days_past_due;
    return days_past_due;
    select ((cast(last_update as date)) - (cast(rental_date as date)) - rental_duration) where ((cast(last_update as date)) - (cast(return_date as date)) - rental_duration) > 0 and return_date is null
        into days_past_due;
    return days_past_due;
end;
$BODY$;

How I populated the past_due_detail table:

insert into past_due_detail(
select rental.customer_id,first_name,last_name,rental_id,rental_date,return_date,title,days_past_due(rental_date,return_date,rental_duration,rental.last_update)
from inventory
inner join film on film.film_id = inventory.film_id
inner join rental on rental.inventory_id = inventory.inventory_id
inner join customer on rental.customer_id = customer.customer_id
where days_past_due(rental_date,return_date,rental_duration,rental.last_update) > 0
order by rental_date,rental.customer_id
);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I took a step back, and accomplished what I wanted by doing the following:

    create or replace function pd_lt3(customer integer,days_overdue smallint)
returns table (pd_lt3 smallint)
language plpgsql
as $$
declare var_r record;
begin
    for var_r in(select customer, days_overdue)      
         loop return query select cast(count(*) as smallint) from past_due_detail where days_past_due < 3 and customer_id = customer;
    end loop;
end;
$$;

  2. 0

    It’s safe to say that your answer is not the solution. A "loop" over a single row built from two input parameters, one of which (days_overdue) not being used at all. Makes no sense. Burns down to just:

    SELECT count(*)::smallint  -- why the cast?
FROM   past_due_detail
WHERE  days_past_due < 3
AND    customer_id = $customer;  -- $customer being your customer_id input.

    If that’s not it, clarify your ambiguous task description.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search