如何调用函数 PostgreSQL

我正在尝试使用 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" = $1;


IF FOUND THEN
UPDATE "USERS"
SET     "name" = $2,
"lastName" = $3,
"userName" = $4,
"password" = $5,
"eMail" = $6
WHERE "userID" = $1;
ELSE
INSERT INTO "USERS"
("name", "lastName", "userName", "password", "eMail")
VALUES
($2, $3, $4, $5, $6);
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 文档指出，要调用不返回任何结果集的函数，只需写入其名称和属性即可。所以我试着这样调用函数:

"saveUser"(3, 'asd','asd','asd','asd','asd');

但我得到了以下错误:

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

我还有其他函数可以返回结果集。我用 SELECT * FROM "fnc"(...)呼叫他们，它工作。为什么我会得到这个错误？

编辑: 我正在使用 pgAdmin III 查询工具并尝试在那里执行 SQL 语句。

206346 次浏览
小开

you declare your function as returning boolean, but it never returns anything.

小开
最佳答案

The function call still should be a valid SQL statement:

SELECT "saveUser"(3, 'asd','asd','asd','asd','asd');
小开

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...);"

小开

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.

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" = $1;
小开

For Postgresql you can use PERFORM. PERFORM is only valid within PL/PgSQL procedure language.

DO $$ BEGIN
PERFORM "saveUser"(3, 'asd','asd','asd','asd','asd');
END $$;

The suggestion from the postgres team:

HINT: If you want to discard the results of a SELECT, use PERFORM instead.

小开

We can have two ways of calling the functions written in pgadmin for postgre sql database.

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:

SELECT "helloworld"('myname');


SELECT public.helloworld('myname')

提交答案