how to eliminate duplicates from my function in postgresql?

Question

I have this vehicle_data table: CREATE TABLE public.vehicle_data

CREATE TABLE vehicle_data
(
  model_name text NOT NULL,
  record_date text NOT NULL,
  inv_quantity integer,
  CONSTRAINT vehicle_data_pkey PRIMARY KEY (model_name, record_date)
)

My table looks like:

model_name  record_date
  car1         5-2015
  car1         1-2016
  car1         2-2015
  car2         2-2017
  car3         8-2016

When I run my function to search for any car, I would like to obtain as a result that orders the entries by month and then by year, so far car1, it should lok like this:

model_name  record_date
  car1         2-2015
  car1         5-2015
  car1         1-2016

Since my record_date is TEXT, I thought that in my function I could split the TEXT array using split_part(record_date,'-',2) to get the year value, store all the unique values in an array, then run my select query for each year.

CREATE OR REPLACE FUNCTION getdata(model text)
   RETURNS TABLE(a text, b text) AS
$BODY$
DECLARE  i int;
     list TEXT[]:= ARRAY(SELECT DISTINCT split_part(record_date,'-',2) as xyz
             from vehicle_data 
             order by xyz);
BEGIN
i:=0;
WHILE i < (select cardinality(list)-1) LOOP 
RETURN QUERY
    select model_name, record_date
    from vehicle_data
    where model_name LIKE model AND split_part(record_date,'-',2) LIKE list[i]
    order by length(record_date), record_date ASC;
i:=i+1;
END LOOP;
RETURN;
END;
$BODY$
  LANGUAGE plpgsql;

Although the function does work, it duplicates the results 68 times, rather than stopping.

Please provide sample data, desired results, and an explanation of the logic you are trying to implement. — Gordon Linoff
– Gordon Linoff, Commented Mar 24, 2019 at 13:09
@a_horse_with_no_name I am fairly new to sql so it's very likely that I've misused this.. — Hanan Ismail
– Hanan Ismail, Commented Mar 24, 2019 at 13:35
Why is car1 1-2016 part of the expected result? It has a different year than the other two rows. I would have expected you only want 2-2015 and 5-2015 in the returned result — user330315
– user330315, Commented Mar 24, 2019 at 13:36
I would like to order records of a particular vehicle for all possible record_dates — Hanan Ismail
– Hanan Ismail, Commented Mar 24, 2019 at 13:38
"I would like to order records of a particular vehicle for all possible record_dates" - then I don't see any reason to check record date at all - just remove that condition completely and you'll get all dates for that car model. Your expected result can be done by simply running select model_name, record_date from vehicle_data where model_name = 'car1' — user330315
– user330315, Commented Mar 24, 2019 at 13:48

score 2 · Accepted Answer · 2019-03-24 14:46:39Z

2

To address your immediate question: to iterate over an array use a FOREACH loop, not a WHILE loop. So you should change your function to something like this:

CREATE OR REPLACE FUNCTION getdata(p_model text)
   RETURNS TABLE(a text, b text) AS
$BODY$
DECLARE  
  l_year text;
  l_year_list TEXT[]:= ARRAY(SELECT DISTINCT split_part(record_date,'-',2) as xyz
                             from vehicle_data);
BEGIN
  foreach l_year in array l_year_list loop
    RETURN QUERY
      select model_name, record_date
      from vehicle_data
      where model_name LIKE p_model 
        AND split_part(record_date,'-',2) = l_year
      order by length(record_date), record_date ASC;
  END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

As no wildcards are involved, I changed the LIKE to an =. I also applied a different naming pattern to the parameters and variables.

But Postgres has really powerful array features in SQL, so the above can be rewritten to a single query without a loop:

CREATE OR REPLACE FUNCTION getdata(p_model text)
   RETURNS TABLE(a text, b text) AS
$BODY$
DECLARE  
  l_year_list TEXT[]:= ARRAY(SELECT DISTINCT split_part(record_date,'-',2) as xyz
                             from vehicle_data);
BEGIN
  RETURN QUERY
    select model_name, record_date
    from vehicle_data
    where model_name LIKE p_model 
      AND split_part(record_date,'-',2) = ANY(l_year_list)
    order by length(record_date), record_date ASC;
END;
$BODY$
  LANGUAGE plpgsql;

This takes all years from the table and returns those rows where the year in the row of the inner quer (only one in case of the second variant) equals to at least one of the values in the array, which is true for all rows, because the values in the array are the all existing years in that column. So at least one of the values in the array will match the one in the row currently being looked at when the query is processed, which in turn means the whole condition isn't really necessary at all.

You can imagine that your function is (logically) being processed like this:

get all years into an array, so the array contains {2015, 2016, 2017}
find all rows where the model name matches. For your example, this leaves us with

model_name record_date car1 5-2015 car1 1-2016 car1 2-2015
Go through the above rows, and see if the year part of the record_date matches any of the dates in the array. That condition will be always true, as the array contains all possible values for that column. So the condition doesn't remove anything from the result.

Which in turn means, that your query is equivalent to:

select vd1.model_name, vd1.record_date
from vehicle_data vd1
where vd1.model_name LIKE = 'car1'
order by split_part(record_date, '-', 2)::int, 
         split_part(record_date, '-', 1)::int;

edited Mar 24, 2019 at 14:46

answered Mar 24, 2019 at 13:34

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Hanan Ismail Over a year ago

thank you so much for your answer!! unfortunately in the first two queries, although the result is correct, it goes on to duplicate it manyyy time, as tho it restarts iterations. With the last query, the problem is that it doesnt order all months for each year, rather it orders all years for all months. so instead of going like '2-2015, 5-2015,1-2016', It goes like '2-2015, 1-2016,5-2015...'

Hanan Ismail Over a year ago

I had actually started from the 3rd, equivalent query that you wrote, but in efforts to order all months for each year before moving onto the next year, i started working on this function

user330315 Over a year ago

@HananIsmail: just change the order by, see my edit in the last query.

Collectives™ on Stack Overflow

how to eliminate duplicates from my function in postgresql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related