Postgres - function matches the given name and argument types. You might need to add explicit type c-CodePudding

I have a function defined within Postgres:

CREATE OR REPLACE FUNCTION public.earned_media_direct(posts bigint, clicks bigint, clicks_multiplier numeric, likes bigint, likes_multiplier numeric, comments bigint, comments_multiplier numeric, reshares bigint, shares_multiplier numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $function$

BEGIN
    RETURN COALESCE(clicks, 0) * clicks_multiplier   
        COALESCE(likes, 0) * likes_multiplier   
        COALESCE(comments, 0) * comments_multiplier   
        (COALESCE(posts, 0)   COALESCE(reshares, 0)) * shares_multiplier;
    
END;

$function$

Tried to use it in a sql query:

earned_media_direct(
            SUM(message_stat.posts_delivered),
            SUM(message_stat.clicks),
            team.earned_media_multi_clicks,
            SUM(message_stat.likes),
            team.earned_media_multi_likes,
            SUM(message_stat.comments),
            team.earned_media_multi_comments,
            SUM(message_stat.shares),
            team.earned_media_multi_shares
        ) AS media_points,
        ROW_NUMBER() OVER (
            ORDER BY earned_media_direct(SUM(message_stat.posts_delivered), SUM(message_stat.clicks), team.earned_media_multi_clicks, SUM(message_stat.likes), team.earned_media_multi_likes, SUM(message_stat.comments), team.earned_media_multi_comments, SUM(message_stat.shares), team.earned_media_multi_shares) DESC
        ) AS rank

And I get an error:

Undefined function: 7 ERROR: function earned_media_direct(bigint, numeric, numeric, bigint, numeric, bigint, numeric, bigint, numeric) does not exist\nLINE 55: earned_media_direct(\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.

Also tried to define it like:

 earned_media_direct(
            SUM(message_stat.posts_delivered)::int,
            SUM(message_stat.clicks)::int,
            team.earned_media_multi_clicks::int,
            SUM(message_stat.likes)::int,
            team.earned_media_multi_likes::int,
            SUM(message_stat.comments)::int,
            team.earned_media_multi_comments::int,
            SUM(message_stat.shares)::int,
            team.earned_media_multi_shares::int
        ) AS media_points,

Can someone say what the problem is? First time I encounter this error. Thanks

CodePudding user response：

clicks is declared as bigint, but you are trying to feed a numeric argument. Since there is no implicit type cast from numeric to bigint, the function is not considered.

Do what the error message recommends and add explicit type casts to the function call.

In addition, you have to make sure that public is on your search_path if you want to call the function without the schema name.