Home > Net >  Postgresql function to return table with change
Postgresql function to return table with change

Time:10-29

OKAY with the recommended change I was able to get a successful query reponse but it throws an error when I call the function.

The error is reading as" column reference "rental" is ambiguous LINE 2: CASE active DETAIL: It could refer to either a PL/PGSQL variable or a table column

CREATE FUNCTION convert()
RETURNS TABLE (customerid int, fname varchar, lname varchar, email varchar, tripDate timestamp, rental varchar)
Language plpgsql
AS $$
BEGIN
     RETURN QUERY
            SELECT CASE WHEN rental = 1 
                        THEN 'yes'
                        ELSE 'no'
             END
     FROM EML
END; $$;

CodePudding user response:

change

SELECT CASE WHEN rental = 1 THEN 'yes'
                        WHEN rental = 0 THEN 'no'

to

SELECT 
    CASE 
        WHEN rental = 1 
            THEN 'yes'
         ELSE  'no'
END

edit section:

if you wan't iterate just binary value, you should be add this condition like this:

RETURN QUERY
         SELECT 
        CASE 
            WHEN rental = 1 
                THEN 'yes'
             ELSE  'no'
    END
 FROM EML WHERE rental In(0, 1)
  • Related