Question 226258 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Inserting Case Statement into CREATE FUNCTION

I’ve been trying to insert a case statement into my create function statement and haven’t had any luck. Just a heads up I am using pgadmin and postgresql. I have a table with rental movie titles, along with an integer that identifies what genre that movie falls in. I am grabbing the genre integer/ category_id from another table and wanted to transform the column data into a String representing the genre name instead of an id number for readibility. I must use a CREATE FUNCTION to transform the column. The code i came up with is below. I am getting the following error:

ERROR: syntax error at end of input LINE 14: $$
^

CREATE OR REPLACE FUNCTION idtoword(category_id INTEGER)
RETURNS VARCHAR(40)
LANGUAGE plpgsql
AS
$$
DECLARE genreWord VARCHAR(40);
BEGIN
SELECT CASE category_id 
WHEN 12 THEN 'COMEDY'
WHEN 1 THEN 'DRAMA'
END
INTO genre
END;
$$

I tried multiple ways to insert the case statement into the create function. Something is wrong with my syntax. I would like the function to take a column of category id’s and return the actual genre name I specify in the case statement

Tags:

2

Answers


  1. 0

    Within a PostgreSQL function, try this code to transform a category ID into its corresponding genre word using a CASE statement.

    CREATE OR REPLACE FUNCTION idtoword(category_id INTEGER)
RETURNS VARCHAR(40)
LANGUAGE plpgsql
AS $$
DECLARE genreWord VARCHAR(40);
BEGIN
    SELECT CASE category_id 
        WHEN 12 THEN 'COMEDY'
        WHEN 1 THEN 'DRAMA'
        ELSE 'UNKNOWN'
    END
    INTO genreWord;
    
    RETURN genreWord;
END;
$$;
    Login or Signup to reply.
  2. 0

    You haven’t added the correct syntax of words as:

    CREATE OR REPLACE FUNCTION idtoword(category_id INTEGER)
RETURNS VARCHAR(40)
LANGUAGE plpgsql
AS $$
DECLARE 
    genreWord VARCHAR(40);
BEGIN
    SELECT CASE category_id 
        WHEN 12 THEN 'COMEDY'
        WHEN 1 THEN 'DRAMA'
        ELSE 'UNKNOWN' 
    END
    INTO genreWord; 
    
    RETURN genreWord;
END;
$$;

    This you have missed as well I have added else unknown here.

    You need to add Into genreWord and same as return genreWord

    I hope this will help you to resolve the error!!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search