PostgreSQL函数返回任意类型

Is there any way to have a function in PostgreSQL return an arbitrary type?

有没有办法在PostgreSQL中有一个函数返回一个任意类型?

I am trying to use PLV8 to grab and compare fields from a JSON type in PostgreSQL 9.2.

我试图使用PLV8来获取和比较PostgreSQL 9.2中JSON类型的字段。

The following works:

以下作品:

CREATE or replace FUNCTION jn (j json, key any ) RETURNS numeric 
LANGUAGE plv8   
IMMUTABLE 
AS $function$  
  var ej = JSON.parse(j);   
  if (typeof ej != 'object') return NULL;   
  return JSON.stringify(ej[key]);  
$function$;

SELECT * FROM things WHERE jn(data,'count') <= '10';

Returning the where the count field is <= 10. BUT, it fails if the field is not numeric. I don't want to create a specific function for every type of data my json might hold, is there a way to have the data type inferred somehow and cast implicitly?

返回计数字段<= 10的位置。但是,如果字段不是数字,则失败。我不想为我的json可能拥有的每种类型的数据创建一个特定的函数,是否有办法以某种方式推断数据类型并隐式转换?

另请参阅如何使用新PostgreSQL JSON数据类型中的字段进行查询?

1 个解决方案

#1

You seem to be looking for functions with Polymorphic types.
Read the above section on types and the last 2 examples in this section.

您似乎在寻找具有多态类型的函数。阅读上面关于类型的部分以及本节中的最后两个示例。

Note, that types of the key parameter and function's output should match. If this is not your case, then you should probably return text and then cast it to the right type outside the function.

注意,关键参数和函数输出的类型应该匹配。如果不是这种情况,那么您应该返回文本,然后将其转换为函数外部的正确类型。

I think this will do the job for you:

我认为这将为您完成这项工作:

CREATE or replace FUNCTION jn (j json, key anyelement ) RETURNS anyelement
LANGUAGE plv8   
IMMUTABLE 
AS $function$  
  var ej = JSON.parse(j);   
  if (typeof ej != 'object') return NULL;   
  return JSON.stringify(ej[key]);  
$function$;

Sorry, I don't have 9.2 server around so I cannot test this.

对不起,我没有9.2服务器,所以我无法测试这个。

EDIT:

Things is — before you will be able to perform any casts within the function, it must be created first. At creation time function's input parameters' types and return type is fixed. This means you have only 2 possibilities:

事情是 - 在你能够在函数中执行任何强制转换之前,必须首先创建它。在创建时,函数的输入参数的类型和返回类型是固定的。这意味着您只有两种可能性:

use anyelement type, which will force you to have key parameter having the same type you want to be returned from the function;

使用anyelement类型,这将强制您使键参数具有您希望从函数返回的相同类型;

use text type and cast results outside the function.

使用文本类型并在函数外部转换结果。

As PLV8 doesn't support anyelement types yet, you can actually create a wrapper function on PL/pgSQL on top of it to do the job for you:

由于PLV8还不支持任何元素类型,您实际上可以在PL / pgSQL上创建一个包装函数来为您完成工作:

CREATE FUNCTION jn_wrap(jn varchar, key anyelement, OUT ret anyelement)
    AS $js_wrap$
BEGIN
    EXECUTE 'SELECT CAST(jn($1, $2) AS '||pg_typeof(key)||')'
        USING jn, key INTO ret;

    RETURN ;
END;
$js_wrap$ LANGUAGE plpgsql;

Though I think it might be quite typical, that key's type will differ from the desired return type.

虽然我认为它可能很典型,但该键的类型将与所需的返回类型不同。

#1