[SOLVED] SQL Function has no destination when called

Issue

I’m getting a success when creating the below function, but when I call it I receive an error stating there is no destination.

CREATE FUNCTION get_top_films() RETURNS int 
language PLPGSQL
AS $$
DECLARE top_films integer;
BEGIN
    SELECT i.film_id,
    COUNT(*)
    FROM rental r
        JOIN inventory i ON r.inventory_id=i.inventory_id
    GROUP BY 1
    ORDER BY COUNT(*) DESC
    LIMIT 10;
RETURN top_films;
END;
$$

Solution

CREATE FUNCTION get_top_films() RETURNS setof inventory.filmid%type
language sql
AS $$
    SELECT i.film_id
    FROM rental r
        JOIN inventory i ON r.inventory_id=i.inventory_id
    GROUP BY 1
    ORDER BY COUNT(*) DESC
    LIMIT 10
$$;

Language SQL: https://www.postgresql.org/docs/current/xfunc-sql.html
language plpgsql: https://www.postgresql.org/docs/current/plpgsql.html
Language sql is more easier to understand. your query is not dynamic, so it’s just a function and $$ query $$.
setof inventory.filmid%type will make it more flexiable. If you filmid type change, this function no need to change.

Answered By – Mark

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.