Return boolean from Hasura PostgreSQL function-CodePudding

I have a subscription table and I want to add a field called is_subscription_active as a computed field on that table. I wrote a function to return a boolean from the PostgreSQL function after looking at some examples, but I can't seem to figure out the issue here.

CREATE OR REPLACE FUNCTION public.is_subscription_active(sub subscriptions)
 RETURNS BOOLEAN
 LANGUAGE sql
 STABLE
AS $function$
    IF (sub.subscription_status = 'active') THEN
        RETURN true;
    ELSE
        IF (sub.subscription_status != 'cancelled') THEN
            RETURN false;
        ELSE
            IF sub.ending_date > now() THEN
                RETURN true;
            ELSE
                RETURN false;
            END IF;
        END IF;
    END IF;
$function$

My logic here is that, I get sub (which will be a row of subscriptions table), and I just check if status is active then return true, else if it's cancelled, then check if the ending date is greater than now, and if it is return true, else false.

I get this error from Hasura.

{
    "statement": "CREATE OR REPLACE FUNCTION public.is_subscription_active(sub subscriptions)\n RETURNS BOOLEAN\n LANGUAGE sql\n STABLE\nAS $function$\n    IF (sub.subscription_status = 'active') THEN\n        RETURN true;\n    ELSE\n        IF (sub.subscription_status != 'canceled') THEN\n            RETURN false;\n        ELSE\n            IF sub.ending_date > now() THEN\n                RETURN true;\n            ELSE\n                RETURN false;\n            END IF;\n        END IF;\n    END IF;\n$function$;",
    "prepared": false,
    "error": {
        "exec_status": "FatalError",
        "hint": null,
        "message": "syntax error at or near \"IF\"",
        "status_code": "42601",
        "description": null
    },
    "arguments": []
}

I've also tried wrapping the function body in a BEGIN ... END block, but I still get the same error.

Clearly, I'm missing something fundamental here, but I can't figure out what

CodePudding user response：

The main issue here is that you're specifying the function language is sql instead of plpgsql. plpgsql is a procedural language specific to postgres that actually allows you to work with procedural coding patterns like conditional statements and loops. These are not supported by regular sql.

The following should work for you:

CREATE OR REPLACE FUNCTION public.is_subscription_active(sub subscriptions)
 RETURNS BOOLEAN
 LANGUAGE plpgsql
 STABLE
AS $function$
    BEGIN
        IF (sub.subscription_status = 'active') THEN
            RETURN true;
        ELSE
            IF (sub.subscription_status != 'cancelled') THEN
                RETURN false;
            ELSE
                IF sub.ending_date > now() THEN
                    RETURN true;
                ELSE
                    RETURN false;
                END IF;
            END IF;
        END IF;
    END;
$function$