Create a function with multiple columns as arguments in Postgresql

Viewed 3353

I am trying to create a function that takes a table and a variable number of columns as arguments, and then returns a table without rows that have duplicates on all of those columns. I am trying to figure out how to have a variable number of columns as arguments, and I have gathered that I will probably need a VARIADIC argument, but I am not sure how to implement it. What I have so far:

CREATE FUNCTION remove_duplicates(orig_table, VARIADIC sel_columns column)
RETURNS table AS $$
    SELECT * FROM 
        (SELECT *,
            count(*) over (partition by sel_columns) AS count
        FROM orig_table)
    WHERE count = 1;
$$ LANGUAGE SQL;

As an example, if I had a table like this:

cola | colb | colc
-------------------
a    | b    | 1
a    | b    | 2
a    | c    | 3
a    | d    | 4

I would like to run SELECT * FROM remove_duplicates(mytable, cola, colb) and get this result:

cola | colb | colc
-------------------
a    | c    | 3
a    | d    | 4

Thank you for the help. I'm using postgresql 9.4.9

1 Answers
Related