postgresql 如何编写返回文本或整数值的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17587735/
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 write a function that returns text or integer values?
提问by 09Q71AO534
I'm using PostgreSQL 9.2.4.
我正在使用 PostgreSQL 9.2.4。
postgres=# select version();
postgres=# 选择版本();
version
-------------------------------------------------------------
PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
(1 row)
My Query executes the insertion safely. What i need is that my function should return something except the void datatype. Something like text("inserted into table") or integer(0-false,1-true) , it will be useful for me to validate whether it is inserted or not?
我的查询安全地执行插入。我需要的是我的函数应该返回除 void 数据类型之外的内容。像 text("inserted into table") 或 integer(0-false,1-true) 之类的东西，验证它是否被插入对我来说很有用？
I need a syntax for a function that returns an integeror a textwhen an insertion is done. For validation purpose. Is there any way to solve this?
我需要一个在插入完成时返回 aninteger或 a的函数的语法text。用于验证目的。有没有办法解决这个问题？
采纳答案by Erwin Brandstetter
What you probably need
你可能需要什么
Most likely you need one function to return textand another one to return integeror a function that returns booleanto indicate success. All of this is trivial and I'll refer you to the excellent manual on CREATE FUNCTIONor code examples in similar questions on SO.
很可能您需要一个函数返回text，另一个函数返回，integer或者一个返回boolean指示成功的函数。所有这些都是微不足道的，我会在关于 SO 的类似问题中向您推荐优秀的手册CREATE FUNCTION或代码示例。
What you actually asked
你实际上问的是什么
How to write a function that returns text or integer values?
如何编写返回文本或整数值的函数？
... in the sense that we have onereturn type being either textor integer. Not as trivial, but also not impossible as has been suggested. The key word is: polymorphic types.
... 从某种意义上说，我们有一个返回类型是textor 或integer。不像所建议的那样微不足道，但也并非不可能。关键词是：多态类型。
Building on this simple table:
建立在这个简单的表上：
CREATE TABLE tbl(
tbl_id int,
txt text,
nr int
);
This function returns either integer or text (or any other type if you allow it), depending on the input type.
此函数返回整数或文本（或任何其他类型，如果您允许），具体取决于输入类型。
CREATE FUNCTION f_insert_data(_id int, _data anyelement, OUT _result anyelement)
RETURNS anyelement AS
$func$
BEGIN
CASE pg_typeof(_data)
WHEN 'text'::regtype THEN
INSERT INTO tbl(tbl_id, txt) VALUES(_id, _data)
RETURNING txt
INTO _result;
WHEN 'integer'::regtype THEN
INSERT INTO tbl(tbl_id, nr) VALUES(_id, _data)
RETURNING nr
INTO _result;
ELSE
RAISE EXCEPTION 'Unexpected data type: %', pg_typeof(_data)::text;
END CASE;
END
$func$
LANGUAGE plpgsql;
Call:
称呼：
SELECT f_insert_data(1, 'foo'::text); -- explicit cast needed.
SELECT f_insert_data(1, 7);
Simple case
简单案例
Onefunction that returns TRUE/ FALSEto indicate whether a row has been inserted, only one input parameter of varying type:
一个返回TRUE/FALSE以指示是否已插入行的函数，只有一个不同类型的输入参数：
CREATE FUNCTION f_insert_data2(_id int, _data anyelement)
RETURNS boolean AS
$func$
BEGIN
CASE pg_typeof(_data)
WHEN 'text'::regtype THEN
INSERT INTO tbl(tbl_id, txt) VALUES(_id, _data);
WHEN 'integer'::regtype THEN
INSERT INTO tbl(tbl_id, nr) VALUES(_id, _data);
ELSE
RAISE EXCEPTION 'Unexpected data type: >>%<<', pg_typeof(_data)::text;
END CASE;
IF FOUND THEN RETURN TRUE;
ELSE RETURN FALSE;
END IF;
END
$func$
LANGUAGE plpgsql;
The input type can be replaced with a textparameter for most purposes, which can be cast to and from any other type.
text大多数情况下，输入类型可以用参数替换，参数可以转换为任何其他类型。
回答by Craig Ringer
It sounds like you're solving a problem by creating a bigger problem.
听起来您正在通过创造更大的问题来解决问题。
You don't need a function for this at all. Do it on the client side by checking the affected rows count that's returned by every DML query, or use INSERT ... RETURNING.
你根本不需要这个功能。通过检查每个 DML 查询返回的受影响行数在客户端执行此操作，或使用INSERT ... RETURNING.
You didn't mention your client language, so here's how to do it in Python with psycopg2. The same approach applies in other languages with syntax variations.
你没有提到你的客户端语言，所以这里是如何在 Python 中使用psycopg2. 相同的方法适用于具有语法变化的其他语言。
#!/usr/bin/env python
import psycopg2
# Connect to the db
conn = psycopg2.connect("dbname=regress")
curs = conn.cursor()
# Set up the table to use
curs.execute("""
DROP TABLE IF EXISTS so17587735;
CREATE TABLE so17587735 (
id serial primary key,
blah text not null
);
""");
# Approach 1: Do the insert and check the rowcount:
curs.execute("""
INSERT INTO so17587735(blah) VALUES ('whatever');
""");
if curs.rowcount != 1:
raise Exception("Argh, insert affected zero rows, wtf?")
print("Inserted {0} rows as expected".format(curs.rowcount))
# Approach 2: Use RETURNING
curs.execute("""
INSERT INTO so17587735(blah) VALUES ('bored') RETURNING id;
""");
returned_rows = curs.fetchall();
if len(returned_rows) != 1:
raise Exception("Got unexpected row count {0} from INSERT".format(len(returned_rows)))
print("Inserted row id is {0}".format(returned_rows[0][0]))
In the case of PL/PgSQL calling INSERTyou can use the GET DIAGNOSTICScommand, the FOUNDvariable, or RETURN QUERY EXECUTE INSERT ... RETURNING .... Using GET DIAGNOSTICS:
在PL / pgSQL的情况下调用INSERT，您可以使用的GET DIAGNOSTICS命令，该FOUND变量，或RETURN QUERY EXECUTE INSERT ... RETURNING ...。使用GET DIAGNOSTICS：
CREATE OR REPLACE FUNCTION blah() RETURNS void AS $$
DECLARE
inserted_rows integer;
BEGIN
INSERT INTO some_table VALUES ('whatever');
GET DIAGNOSTICS inserted_rows = ROW_COUNT;
IF inserted_rows <> 1 THEN
RAISE EXCEPTION 'Failed to insert rows; expected 1 row, got %', inserted_rows;
END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;
or if you must return values and must for some reason use PL/PgSQL:
或者如果您必须返回值并且出于某种原因必须使用 PL/PgSQL：
CREATE OR REPLACE FUNCTION blah() RETURNS SETOF integer AS $$
BEGIN
RETURN QUERY EXECUTE INSERT INTO some_table VALUES ('whatever') RETURNING id;
END;
$$ LANGUAGE plpgsql VOLATILE;
(assuming the key is id)
（假设密钥是id）
which would be the same as:
这将与：
CREATE OR REPLACE FUNCTION blah() RETURNS SETOF integer AS $$
INSERT INTO some_table VALUES ('whatever') RETURNING id;
$$ LANGUAGE sql;
or just
要不就
INSERT INTO some_table VALUES ('whatever') RETURNING id;
In other words: Why wrap this in a function? It doesn't make sense. Just check the row-count client side, either with RETURNINGor by using the client driver's affected-rows count for INSERT.
换句话说：为什么要把它包装在一个函数中？这没有意义。只需检查行计数客户端，使用RETURNING或使用客户端驱动程序的受影响行计数INSERT。
回答by Peter Eisentraut
A function can only return one type. In your case, you could create a composite type with two fields, one integer and one text, and return that.
一个函数只能返回一种类型。在您的情况下，您可以创建一个包含两个字段的复合类型，一个整数和一个文本，然后返回它。
