Issues creating a function

Thank you for reading my post and trying to assist.

I am pretty new with PostgreSQL and am struggling with trying to create a Function to transform a table column that is currently integer (1,0) into ‘Yes’ or ‘No” and redisplaying the table after the change, what’s your recommendation on how best to do this?

The below will run and show a successful query but when I actually try to run it with SELECT convert_active(); It is not working. I have also tried to do it using CASE but cant get that one to work either. Please help!

    CREATE or REPLACE FUNCTION convert_active()
Returns TABLE (customer_id int, 
      first_name varchar,
    last_name varchar,
    email varchar,
    rental_date timestamp,
    active varchar)
Language plpgsql
As $$
Begin
RETURN QUERY
SELECT CASE WHEN active = 1 THEN ‘Yes’
         ELSE  ‘No’
END
 
FROM email_marketing_list;
END; $$;

Answer

Your function query has two problems:

1. If you init column in function structure in returns query you have to return column in the query, and in your query just return CASE WHEN active = 1 THEN ‘Yes’ ELSE ‘No’

2. In your function you add active varchar and in query need cast to varchar

CREATE or REPLACE FUNCTION convert_active()
    RETURNS TABLE
            (
                customer_id INT,
                first_name  VARCHAR,
                last_name   VARCHAR,
                email       VARCHAR,
                rental_date TIMESTAMP,
                active      VARCHAR
            )
    LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN QUERY
        SELECT eml.customer_id,
               eml.first_name,
               eml.last_name,
               eml.email,
               eml.rental_date,
               (CASE
                   WHEN eml.active = 1 THEN 'Yes'
                   ELSE 'No'
                   END)::VARCHAR

        FROM email_marketing_list eml;
END;
$$;