Postgres: Function check duplicates in table

Question

I have table with multiple duplicates and I want to make from these a function. Could you please help me to make a function from this code? thanks.

SELECT id_member,id_service,amount,date, count(*) as number_of_duplicates
from evidence
GROUP BY id_member,id_service,amount,date
HAVING COUNT(*) > 1;


CREATE OR REPLACE FUNCTION check_for_duplicates()
RETURNS VOID AS
$BODY$
BEGIN
SELECT id_member,id_service,amount,date, count(*) as number_of_duplicates
from evidence
GROUP BY id_member,id_service,amount,date
HAVING COUNT(*) > 1;
END;
$BODY$
LANGUAGE ‘plpgsql‘;

Yes. I have trouble with creating function from these peace of code. — nwind
– nwind, Commented Jan 29, 2018 at 12:57
I think its because you return VOID and have no out parameter - try returning a table instead?.. although I don't understand why you want to wrap it up to plpgsql - no arguments, no variables... — Vao Tsun
– Vao Tsun, Commented Jan 29, 2018 at 13:00

user330315 · Accepted Answer · 2018-01-29 13:06:24Z

If a function should return a result set it needs to be declared as returns table () or returns setof

You also don't need a PL/pgSQL function for that, a simple SQL function will do and is more efficient:

CREATE OR REPLACE FUNCTION check_for_duplicates()
RETURNS table (id_member integer, id_service integer, amount numeric, date date, number_of_duplicates bigint) 
$BODY$
  SELECT id_member,id_service,amount,date, count(*) as number_of_duplicates
  from evidence
  GROUP BY id_member,id_service,amount,date
  HAVING COUNT(*) > 1;
$BODY$
LANGUAGE sql;

You didn't show us the definition of the table evidence so I had to guess the data type of the columns. You will need to adjust the types in the returns table (...) part to match the types from the table.

Having said that: I would create a view for things like that, not a function.

^{Unrelated, but: date is a horrible name for a column. For one because it's also a keyword but more importantly it doesn't document what the column contains: a release date? An expiration date? A due date?}

Collectives™ on Stack Overflow

Postgres: Function check duplicates in table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related