如何调用函数，PostgreSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1953326/
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 call a function, PostgreSQL
提问by Erkan Haspulat
I'm trying to use a function with PostgreSQL to save some data. Here is the create script:
我正在尝试使用带有 PostgreSQL 的函数来保存一些数据。这是创建脚本：
-- Function: "saveUser"(integer, character varying, character varying, character varying, character varying, character varying)
-- DROP FUNCTION "saveUser"(integer, character varying, character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION "saveUser"("pUserID" integer, "pName" character varying, "pLastName" character varying, "pUserName" character varying, "pPassword" character varying, "peMail" character varying)
RETURNS boolean AS
$BODY$
BEGIN
SELECT 1
FROM "USERS"
WHERE "userID" = ;
IF FOUND THEN
UPDATE "USERS"
SET "name" = ,
"lastName" = ,
"userName" = ,
"password" = ,
"eMail" =
WHERE "userID" = ;
ELSE
INSERT INTO "USERS"
("name", "lastName", "userName", "password", "eMail")
VALUES
(, , , , );
END IF;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION "saveUser"(integer, character varying, character varying, character varying, character varying, character varying) OWNER TO postgres;
PostreSQL Documentation states that to call a function which does not return any resultset, it is sufficient to write only its name and properties. So I try to call the function like this:
PostreSQL 文档指出，调用一个不返回任何结果集的函数，只写它的名称和属性就足够了。所以我尝试像这样调用函数：
"saveUser"(3, 'asd','asd','asd','asd','asd');
But I get the error below:
但我收到以下错误：
ERROR: syntax error at or near ""saveUser""
LINE 1: "saveUser"(3, 'asd','asd','asd','asd','asd')
^
********** Error **********
ERROR: syntax error at or near ""saveUser""
SQL state: 42601
Character: 1
I have other functions which return a resultset. I use SELECT * FROM "fnc"(...)to call them and it works. Why am I getting this error?
我还有其他返回结果集的函数。我SELECT * FROM "fnc"(...)过去常常打电话给他们，它的工作原理。为什么我收到这个错误？
EDIT:I am using pgAdmin III Query tool and trying to execute the SQL Statements there.
编辑：我正在使用 pgAdmin III 查询工具并尝试在那里执行 SQL 语句。
回答by Milen A. Radev
The function call still should be a valid SQL statement:
函数调用仍然应该是一个有效的 SQL 语句：
SELECT "saveUser"(3, 'asd','asd','asd','asd','asd');
回答by itsnikolay
For Postgresql you can use PERFORM. PERFORM is only valid within PL/PgSQL procedure language.
对于 Postgresql，您可以使用PERFORM。PERFORM 仅在 PL/PgSQL 过程语言中有效。
DO $$ BEGIN
PERFORM "saveUser"(3, 'asd','asd','asd','asd','asd');
END $$;
The suggestion from the postgres team:
postgres 团队的建议：
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
提示：如果您想丢弃 SELECT 的结果，请改用 PERFORM。
回答by Krutik
We can have two ways of calling the functions written in pgadmin for postgre sql database.
我们可以有两种方式调用 pgadmin 中为 postgre sql 数据库编写的函数。
Suppose we have defined the function as below:
假设我们定义了如下函数：
CREATE OR REPLACE FUNCTION helloWorld(name text) RETURNS void AS $helloWorld$
DECLARE
BEGIN
RAISE LOG 'Hello, %', name;
END;
$helloWorld$ LANGUAGE plpgsql;
We can call the function helloworld in one of the following way:
我们可以通过以下方式之一调用函数 helloworld：
SELECT "helloworld"('myname');
SELECT public.helloworld('myname')
回答by pengli
if your function does not want to return anything you should declare it to "return void" and then you can call it like this "perform functionName(parameter...);"
如果您的函数不想返回任何内容，您应该将其声明为“return void”，然后您可以像这样调用它“perform functionName(parameter...);”
回答by G.Bouch
I had this same issue while trying to test a very similar function that uses a SELECT statement to decide if a INSERT or an UPDATE should be done. This function was a re-write of a T-SQL stored procedure.
When I tested the function from the query window I got the error "query has no destination for result data". I finally figured out that because I used a SELECT statement inside the function that I could not test the function from the query window until I assigned the results of the SELECT to a local variable using an INTO statement. This fixed the problem.
我在尝试测试一个非常相似的函数时遇到了同样的问题，该函数使用 SELECT 语句来决定是否应该执行 INSERT 或 UPDATE。此函数是对 T-SQL 存储过程的重写。
当我从查询窗口测试该函数时，出现错误“查询没有结果数据的目的地”。我终于发现，因为我在函数内部使用了 SELECT 语句，所以在使用 INTO 语句将 SELECT 的结果分配给局部变量之前，我无法从查询窗口测试该函数。这解决了问题。
If the original function in this thread was changed to the following it would work when called from the query window,
如果此线程中的原始函数更改为以下内容，则从查询窗口调用时它将起作用，
$BODY$
DECLARE
v_temp integer;
BEGIN
SELECT 1 INTO v_temp
FROM "USERS"
WHERE "userID" = ;
回答by chburd
you declare your function as returning boolean, but it never returns anything.
您将函数声明为返回布尔值，但它从不返回任何内容。
