从带有 OUT 参数的函数返回 [英] Returning from a function with OUT parameter
问题描述
我有一个错误,但我不知道问题出在哪里.
我想执行一个函数并从由列默认填充的列返回一个值,一个序列 - 相当于 currval(sequence)
.
我使用:
PostgreSQL 9.0
pgAdmin III
CREATE OR REPLACE FUNCTION name_function(在 param_1 字符中变化, 出 param_2 bigint)作为$$开始INSERT INTO 表 (collumn_seq,param_1) VALUES (DEFAULT,param_1)返回 collumn_seq;--where:collumn_seq 引用一个列序列..结尾;$$语言 plpgsql 易失性;
我可以创建没有错误的函数,但是在尝试执行时,返回以下错误:
<块引用>SELECT name_function('GHGHGH');错误:查询没有结果数据的目标
它会像这样工作:
CREATE OR REPLACE FUNCTION name_function(param_1 varchar, OUT param_2 bigint)语言 plpgsql AS$func$开始INSERT INTO table (collumn_seq, param_1) -- param_1"也是列名?值(默认,param_1)返回 collumn_seq进入参数2;结尾$func$;
通常,您会添加一个 RETURN
语句,但是对于 OUT
参数,这是可选的.
请参阅手册了解更多详情:
简单的情况可以用一个普通的 SQL 函数来覆盖.
您可以省略应获得其 DEFAULT
值的目标列.
在这种情况下,您也可以使用 RETURNS
子句:
CREATE OR REPLACE FUNCTION name_function(param_1 varchar)返回 bigint语言 sql AS$func$INSERT INTO table (param_1) -- "param_1";还有列名?值(参数 1)返回 collumn_seq;$func$;
I have an error, but I don't know what the problem is.
I want execute a function and return a value from a column filled in by the column default, a sequence - the equivalent of currval(sequence)
.
I use:
PostgreSQL 9.0
pgAdmin III
CREATE OR REPLACE FUNCTION name_function(in param_1 character varying
, out param_2 bigint)
AS
$$
BEGIN
INSERT INTO table (collumn_seq,param_1) VALUES (DEFAULT,param_1)
returning collumn_seq;
--where:collumn_seq reference a collumn serial..
END;
$$
LANGUAGE plpgsql VOLATILE;
I can create the function without error but when trying to execute, the following error is returned:
SELECT name_function('GHGHGH'); ERROR: The query has no destination for result data
It would work like this:
CREATE OR REPLACE FUNCTION name_function(param_1 varchar
, OUT param_2 bigint)
LANGUAGE plpgsql AS
$func$
BEGIN
INSERT INTO table (collumn_seq, param_1) -- "param_1" also the column name?
VALUES (DEFAULT, param_1)
RETURNING collumn_seq
INTO param2;
END
$func$;
Normally, you would add a RETURN
statement, but with OUT
parameters, this is optional.
Refer to the manual for more details:
The simple case can be covered with a plain SQL function.
And you can omit the target column that shall get its DEFAULT
value.
And you can just as well use a RETURNS
clause in this case:
CREATE OR REPLACE FUNCTION name_function(param_1 varchar)
RETURNS bigint
LANGUAGE sql AS
$func$
INSERT INTO table (param_1) -- "param_1" also the column name?
VALUES (param_1)
RETURNING collumn_seq;
$func$;
这篇关于从带有 OUT 参数的函数返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!