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

Why isn't my BEGIN command working on SQL – Postgresql

I have this code to create a function. But it tells me the command in BEGIN is wrong

CREATE FUNCTION transform_activebool(activebool CHAR(1)) 
RETURNS VARCHAR(10)
BEGIN
    IF activebool = 't' THEN
        RETURN 'Active';
    ELSE
        RETURN 'Inactive';
    END IF;
END;

Code error message

I tried moving the syntax and looking at the BEGIN command and it’s not working.

Tags:

2

Answers


  1. 0

    try this:

    CREATE FUNCTION transform_activebool(activebool CHAR(1)) 
RETURNS VARCHAR(10)
language plpgsql
as
$$
begin
    IF activebool = 't' THEN
        RETURN 'Active';
    ELSE
        RETURN 'Inactive';
    END if;
END;
$$
    Login or Signup to reply.
  2. 0

    Use valid syntax. In this case you can use a simple SQL function, using a CASE to select the correct output:

    CREATE FUNCTION transform_activebool(activebool CHAR(1)) 
RETURNS VARCHAR(10)
LANGUAGE SQL -- you always have to define the language
STRICT -- RETURNS NULL on NULL input
IMMUTABLE -- always returns the same output for the same input
AS
$$ -- function body starts here:
    SELECT
        CASE 
            WHEN activebool = 't' 
                THEN 'Active'
            ELSE    'Inactive'
        END CASE;
$$; -- end of the function body

-- test:
SELECT transform_activebool('t') -- Active
    , transform_activebool('x') -- Inactive
    , transform_activebool(NULL); -- NULL

    This function is so simple, there is no need for the plpgsql language and thus no need for BEGIN and END.

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

Back To Top
Search