Issues creating a function

Question

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

Why do you want to create a function? What have you tried so far? You can use a `case` expression https://www.postgresql.org/docs/14/functions-conditional.html as in case `column when 1 then 'Yes' else 'No' end`. Make sure you consider null. You can either create a new table with the new column `create table t as select...` or add a new column with `alter table ...` then drop the old column, and if you are brave rename the new column to the old column. — Allan Wind, Oct 30 '21 at 01:02

score 0 · Accepted Answer · answered Oct 30 '21 at 05:58

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

Thank you that worked great, and I understand what I missed. — Michele Ann Riedel, Oct 31 '21 at 16:03

Issues creating a function

1 Answers1