如何编写一个返回文本或整数值的函数? [英] How to write a function that returns text or integer values?

问题描述

我正在使用PostgreSQL 9.2.4.

I'm using PostgreSQL 9.2.4.

postgres =#选择版本();

postgres=# select version();

                           version
-------------------------------------------------------------
 PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
(1 row)

sqlfiddle链接

我的查询安全地执行插入.我需要的是我的函数应该返回除void数据类型以外的其他内容.诸如text("insertted into table")或integer(0-false，1-true)之类的东西，对我验证是否插入它很有用?

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?

我需要一个函数的语法，该函数在插入完成后返回integer或text.用于验证目的.有什么办法解决这个问题?

I need a syntax for a function that returns an integer or a text when an insertion is done. For validation purpose. Is there any way to solve this?

推荐答案

您可能需要的内容

很可能您需要一个函数返回text，而另一个函数返回integer，或者返回boolean的函数表示成功.所有这些都是微不足道的，我将带您参考关于CREATE FUNCTION 或类似示例中关于SO的代码示例

What you probably need

Most likely you need one function to return text and another one to return integer or a function that returns boolean to indicate success. All of this is trivial and I'll refer you to the excellent manual on CREATE FUNCTION or code examples in similar questions on SO.

如何编写返回文本或整数值的函数?

How to write a function that returns text or integer values?

...从某种意义上说，我们有一个 返回类型为text或integer.不像建议的那样琐碎，但也不是没有可能.关键字是: 多态类型 .

... in the sense that we have one return type being either text or integer. Not as trivial, but also not impossible as has been suggested. The key word is: polymorphic types.

基于此简单表:

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;

致电:

SELECT f_insert_data(1, 'foo'::text);  -- explicit cast needed.
SELECT f_insert_data(1, 7);

简单案例

一个函数，该函数返回TRUE/FALSE来指示是否已插入行，只有一个输入类型不同的输入参数:

Simple case

One function that returns TRUE / FALSE to indicate whether a row has been inserted, only one input parameter of varying type:

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;

在大多数情况下，输入类型都可以用text参数替换，该参数可以与其他任何类型进行强制转换.

The input type can be replaced with a text parameter for most purposes, which can be cast to and from any other type.

这篇关于如何编写一个返回文本或整数值的函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！