将数组传递给存储给Postgres [英] Passing an array to stored to postgres

问题描述

就像劝我链接到我的previous问题：结果
<一href=\"http://stackoverflow.com/questions/27708234/pl-pgsql-control-structures-for-lists-arrays\">PL/pgSQL对列表控制结构/阵列

Like advised I am linking to my previous question:
PL/pgSQL control structures for lists / arrays

我想通过数字列表和字符列表到存储过程的Postgres。从那以后，我想用这两个数组的东西。对于号码列表你已经帮了我。我用 CREATE OR REPLACE FUNCTION MY_PROC（p_amount_list数字[]）作为输入参数，当我叫我用 SELECT MY_PROC（{2该程序，2,2}'）;

I want to pass a list of numbers and a list of characters into a Postgres stored procedure. After that I want to do something with those two arrays. For number list you already helped me. I used CREATE OR REPLACE FUNCTION my_proc(p_amount_list numeric[]) as input parameter, and when I called that procedure I used SELECT my_proc('{2,2,2}');.

现在我想知道如何通过人物的名单，以及如何后调用过程。

Now I want to know how to pass a list of characters and also how to call the procedure after that.

推荐答案

您的可能的建立一个功能，要么符连接字符串或总结取决于多态输入类型的数值：

You could build a function that either concatenates strings or sums up numeric values depending on the polymorphic input type:

CREATE OR REPLACE FUNCTION poly_sum_agg(_arr anyarray)
  RETURNS text AS
$func$
BEGIN

CASE (SELECT e.typcategory FROM pg_type e
      WHERE  e.oid = (SELECT typelem FROM pg_type WHERE oid = pg_typeof(_arr)))

WHEN 'S' THEN  -- string types: char, name, text, bpchar, varchar
   RETURN array_to_string(_arr, '');  -- no separator?

WHEN 'N' THEN  -- numeric: int8, int2, int4, oid, float4, float8, money, numeric
   RETURN (SELECT sum(elem)::text FROM unnest(_arr) elem);

ELSE
   RAISE EXCEPTION 'Unexpected array type!';

END CASE;

END
$func$ LANGUAGE plpgsql;

呼叫（注意明确的类型转换）

SELECT poly_sum_agg('{1,2,3}'::int[]) AS int_arr; -- Returns '6'

或者

SELECT poly_sum_agg('{1,2,3}'::text[]) AS text_arr; -- Returns '123'

SQL小提琴。

这只是一个概念验证。它建立在系统目录中的 >和之间可能不是主要版本移植。这里是我如何检索来回系统目录信息：

This is just a proof of concept. It builds on the internal representation of array types in the system catalog pg_type and may not be portable across major releases. Here is how I retrieved information fro the system catalog:

SQL小提琴。

这在很大程度上是不清楚您的具体目标是什么。根据你的实际需要，我想最有可能采取不同的方法。

It's largely unclear what your exact objective is. Depending on what you actually need, I would most probably take a different approach.

这篇关于将数组传递给存储给Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！