SQL Function has no destination when called-CodePudding

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;
$$

CodePudding user response：

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.

CodePudding user response：

To extend on my comment:

CREATE OR REPLACE FUNCTION get_top_films()
RETURNS SETOF INTEGER -- or inventory.filmid%TYPE
AS $$
DECLARE
BEGIN
    RETURN QUERY
    SELECT i.film_id
      FROM rental r
      JOIN inventory i ON r.inventory_id = i.inventory_id
     GROUP BY i.film_id
     ORDER BY COUNT(*) DESC
     LIMIT 10
    ;
END;
$$  LANGUAGE PLPGSQL;