Issues creating a function-CodePudding

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

CodePudding user response：

Your function query has two problems:

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

CodePudding user response：

Better make a view than a function, as simple as

create or replace view email_marketing_list_v as
SELECT customer_id, first_name, last_name, email,rental_date,
  case when active = 1 then 'Yes' when active = 0 then 'No' end ::text as active
FROM email_marketing_list;

A view would not defeat indexes like a function will and is not an optimization barrier.
Having cases for both 1 and 0 will handle nulls correctly.