postgresql 如何在postgresql中直接执行存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23293986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute a stored procedure directly in postgresql?
提问by Debs
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','[email protected]',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
错误：函数 insert_user_ax_register(unknown, unknown, unknown, timestamp with time zone, unknown, unknown, timestamp with time zone, unknown, unknown, unknown, bigint) 不存在 SQL 状态：42883 提示：没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。人物：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
请帮我解决这个问题。我是 pgsql 的新手，无法从谷歌找到任何解决方案。我在用pgsql 9.1.3
May I know what is the correct way to achieve my objective?
我可以知道实现目标的正确方法是什么吗？
回答by a_horse_with_no_name
The error message tells you what you need to be looking for:
错误消息告诉您需要查找的内容：
"No function matches the given name and argument types"
“没有函数匹配给定的名称和参数类型”
As the function nameseems 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) --> '[email protected]' 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_dateas date, not as varcharor adjust the values that you pass for each parameter.
因此，您需要调整参数类型，例如定义join_date为日期，而不是varchar调整为每个参数传递的值。
And finally you need to call the function like this:
最后你需要像这样调用函数：
SELECT insert_user_ax_register(...);
rather than select * from ...
而不是 select * from ...
回答by Ankur Srivastava
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.
如果您正在使用 pgAdmintool，只需右键单击架构下的函数或存储过程，然后选择属性，然后选择 paramaeters 。现在插入您想要插入的值。
