How to execute a stored procedure directly in postgresql?

Question

I have 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 like this:

SELECT * from insert_user_ax_register('debasrita','debasrita','client001',now(),'t','abc@gmail.com',now(),'ctc','debasrita','2014-15',9090909090);

but it throws the following error :

ERROR: function insert_user_ax_register(unknown, unknown, unknown, timestamp with time zone, unknown, unknown, timestamp with time zone, unknown, unknown, unknown, bigint) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 16

Please help me out on this. I am new to pgsql and not able to find out any solution from google. I am using pgsql 9.1.3

May I know what is the correct way to achieve my objective?

Its still not working @a_horse_with_no_name – Debs Apr 25 '14 at 13:14 — Debs, Apr 25 '14 at 13:14

score 14 · Accepted Answer · answered Apr 25 '14 at 13:34

The error message tells you what you need to be looking for:

"No function matches the given name and argument types"

As the function name seems correct, it can only be the parameters you are passing. So write down which 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)           --> 'abc@gmail.com'
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 adjust the parameter types, e.g. define join_date as date, not as varchar or adjust the values that you pass for each parameter.

And finally you need to call the function like this:

SELECT insert_user_ax_register(...);

rather than select * from ...

Hey thanks!! You rectified my mistake :) But i must say that select * from ... works as well. — Debs, Apr 26 '14 at 05:11

score -1 · Answer 2 · answered Jan 10 '17 at 17:10

-1

If you are using pgAdmintool just right click on the function or Stored Proc under a schema and select properties and then paramaeters .Now insert the value which you wannna insert.

answered Jan 10 '17 at 17:10

Ankur Srivastava

855
9
10

How to execute a stored procedure directly in postgresql?

2 Answers2