How to execute a stored procedure directly in postgresql?

Question

I created a procedure like:

CREATE OR REPLACE FUNCTION insert_user_ax_register( user_name character varying(50), password character varying(300), role_id character varying(10), created_dt date, status boolean, email character varying(50), join_date character varying(30), phone_no bigint, client_address character varying(200), full_name character varying(100), financial_year character varying(10)) RETURNS void AS $BODY$ declare begin INSERT INTO ax_register(user_name,password,role_id,created_dt,status,email,join_date,phone_no,client_address,full_name,financial_year) VALUES (user_name,password,role_id,now(),true,email,join_date,phone_no,client_address,full_name,financial_year); end $BODY$ LANGUAGE plpgsql VOLATILE 

and tried to execute it as follows:

 SELECT * from insert_user_ax_register('debasrita','debasrita','client001',now(),'t',' [e-mail] ',now(),'ctc','debasrita','2014-15',9090909090); 

but it produces the following error:

ERROR: function insert_user_ax_register (unknown, unknown, unknown, time zone with time zone, unknown, unknown, timestamp with time zone, unknown, unknown, unknown, bigint) does not exist SQL state: 42883 Hint: no function matches the specified name and types of arguments. You may need to add explicit types. Character: 16

Please help me with this. I am new to pgsql and cannot find any solution from google. I am using pgsql 9.1.3

Can I find out what is the right way to achieve my goal?

Answer 1

The error message tells you what you need to look for:

"No function matches the specified name and argument types "

Since the function name seems correct, it can only be those parameters that you pass. Therefore, write down what value is passed for which parameter:

  'debasrita' -> user_name character varying (50)
 'debasrita' -> password character varying (300)
 'client001' -> role_id character varying (10)
 created_dt date -> now ()
 status boolean, -> 't'
 email varchar (50) -> ' [e-mail] '
 join_date varchar (30) -> now () << first error: now () is not a character constant
 phone_no bigint -> 'ctc' << second error: 'ctc' is not a bigint
 client_address varchar (200) -> 'debasrita'
 full_name varchar (100) -> '2014-15'
 financial_year varchar (10) -> 9090909090 << third error: 9090909090 is not a character literal

So, you need to either configure parameter types, for example. define join_date as a date, not as varchar or adjust the values ​​you pass for each parameter.

And finally, you need to call the function as follows:

 SELECT insert_user_ax_register(...); 

not select * from ...

Answer 2

If you are using pgAdmintool, just right-click on the function or saved Proc within the schema and select properties and then paramaeters. Now insert the value you are inserting.