CODEWITHSUNDEEP
postgresqlamazon-rds
Raphael Heard
Raphael Heard

Reputation: 99

Unable to get Functions Created successfully in AWS RDS Instance running PostgreSQL 16.3

I have been trying to get Functions created successfully in my AWS RDS Instance running PostgresSQL 16.3. I keep getting the same types of errors. "$$" is causing the errors, and can't figure out the correct syntax. I have created a local instance of PostgreSQL and get the same errors. The syntax is the correct one on the PostgreSQL website. I have enclosed the code and the error.:

Code:

CREATE OR REPLACE FUNCTION public.get_current_user_id()
    RETURNS uuid
    LANGUAGE plpgsql
   AS 
$$
    SELECT current_setting('app.current_user_id', true)::uuid;
$$;

CREATE OR REPLACE FUNCTION public.user_has_role(p_role_names text[])
    RETURNS boolean
    LANGUAGE plpgsql
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM public.um_user_roles ur
        JOIN public.um_roles r ON ur.role_id = r.id
        WHERE ur.user_id = public.get_current_user_id()
        AND r.role_name = ANY(p_role_names)
        AND ur.status = 'active'
        AND (ur.valid_until IS NULL OR ur.valid_until > CURRENT_TIMESTAMP)
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Error:

Error at line 37:
Statement: CREATE OR REPLACE FUNCTION public.get_current_user_id() RETURNS uuid LANGUAGE plpgsql AS $$ DECLARE BEGIN RETURN current_setting('app.current_user_id', true)::uuid;
Error message: unterminated dollar-quoted string at or near "$$ DECLARE BEGIN RETURN current_setting('app.current_user_id', true)::uuid;"
LINE 1: ...urrent_user_id() RETURNS uuid LANGUAGE plpgsql AS $$ DECLARE...
                                                             ^

Upvotes: 0

Views: 34

Answers (0)

Related Questions