PostgreSQL: execute a stored function (getting an error even though the arguments have been casted)

Question

I've run into a problem I couldn't find a solution to.

I've successfully created a function. Here's the function name and arguments:

CREATE OR REPLACE FUNCTION app_private.create_user(username citext, email text, email_is_verified boolean, name text, avatar_url text, title text DEFAULT NULL::text, color character varying DEFAULT NULL::character varying, user_role smallint DEFAULT 0, password text DEFAULT NULL::text)
RETURNS app_public.users
....
CREATE FUNCTION

I've also successfully granted privilege to execute the function to a role. AND also created a comment:

grant execute on function app_private.create_user(username citext, email text, email_is_verified bool, name text, avatar_url text, title text, color varchar(7), user_role smallint, password text) to nine_manager;
GRANT    
comment on function app_private.create_user(username citext, email text, email_is_verified bool, name text, avatar_url text, title text, color varchar(7), user_role smallint, password text) is  
    E'Creates a user account.';
COMMENT

however, when I try to create a test user by querying:

SELECT "app_private.create_user"('JS0'::citext, '[email protected]'::text, true::boolean, 'John Smith'::text, 'SY'::text, 'Manager'::text, '#000000'::varchar(7), 5::SMALLINT, 'test'::text);

I get an error:

ERROR: function app_private.create_user(citext, text, boolean, text, text, text, character varying, smallint, text) does not exist
LINE 1: SELECT "app_private.create_user"('SY0'::citext, 'test@gmail....

I've tried changing the queries and casts but failed. Nearly pulling my hair out.

Thank you ahead of time.

SELECT "app_private"."create_user" or remove all those dreaded double quotes completely — user330315
– user330315, Commented Jan 28, 2020 at 10:27
That did the trick. Thank you. I read somewhere to add double quotes for names with a period in between, oops! — Real Yang
– Real Yang, Commented Jan 28, 2020 at 17:40

Himanshu · Accepted Answer · 2020-01-28 10:29:46Z

1

Remove double qoutes before calling your function in select as below

     Select app_private.create_user(....)
      From table;

answered Jan 28, 2020 at 10:29

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Real Yang Over a year ago

Thank you so much! Removing the quotes worked! (I feel like an idiot...)

Collectives™ on Stack Overflow

PostgreSQL: execute a stored function (getting an error even though the arguments have been casted)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related