Tried calling a user-defined function but got an error

Question

I tried creating a user-defined function (in postgreSQL) that allows you to transform some strings from lower to uppercase. The dataset I'm using is from a 'DVD rental business' sample database in a virtual lab environment my school is providing me.

Here's the code:

CREATE OR REPLACE FUNCTION get_uppercase_film_names()
RETURNS TABLE (film_id INTEGER, uppercase_name CHARACTER VARYING(255)) AS

$$
BEGIN
     RETURN QUERY 
     SELECT film.film_id, UPPER(film.title) AS uppercase_name
     FROM film;
END;
$$
LANGUAGE plpgsql;

When I run this, it is successful.

When I go to call the function:

SELECT film_id, uppercase_name FROM get_uppercase_film_names();

I get an error message pgadmin4:

ERROR: structure of query does not match function result type
DETAIL: Returned type text does not match expected type character varying in column 2.
CONTEXT: PL/pgSQL function get_uppercase_film_names() line 3 at RETURN QUERY 
SQL state: 42804

This is strange because when I do a select query on the film table, the title is definitely a character varying(255) datatype. It does match but not according to this error message. I'm totally lost so any help will be much appreciated.

I did try casting the return query with:

SELECT film.film_id, UPPER(film.title::VARCHAR(255)) AS uppercase_name

but I got the same error message after trying to call the function.

Answer 1

Try modifying your CHARACTER VARYING(255) to TEXT as it is generally a safe choice, and should help avoid any mismatch issues you’re having.

CREATE OR REPLACE FUNCTION get_uppercase_film_names() 
RETURNS TABLE (film_id INTEGER, uppercase_name TEXT)
AS
$$ 
BEGIN 
    RETURN QUERY 
    SELECT film.film_id, UPPER(film.title) AS uppercase_name 
    FROM film; 
END; 
$$ 
LANGUAGE plpgsql;