PostgreSQL 8.2 -- 如何获得任何数组的字符串表示? [英] PostgreSQL 8.2 -- How to get a string representation of any array?
问题描述
在PostgreSQL8.2中，PostgreSQL 的文本到数组转换的逆是什么?
众所周知:
select '{a,b,c}'::text[] as x, '{{0,1},{2,3}}'::int[][] as y;/** x |是* ---------+--------------* {a,b,c} |{{0,1},{2,3}}*/是否有预定义的函数 f(anyarray) 或运算符来执行逆运算?通过反向"，我的意思是应用 f(x)::text[] 或 f(y)::int[][] 会将它带回数组形式.
以下是一个假设的例子，只是为了说明这一点.
select 'x='||f(x) as x_str, 'y'=f(y) as y_str从 (选择 '{a,b,c}'::text[] 作为 x, '{{0,1},{2,3}}'::int[][] 作为 y;) 作为一个;/** x |是* -----------+-----------------* x={a,b,c} |y={{0,1},{2,3}}*/编辑:不幸的是(相信我，这是我在这里浪费时间之前尝试过的第一件事)，select x::text 对我不起作用(因为我坚持使用 PostgreSQL8.2——我使用 Greenplum):
test=>SELECT ('{a,b,c}'::text[])::text;错误:无法将类型 text[] 转换为 text第 1 行:SELECT ('{a,b,c}'::text[])::text;^编辑#2:与某些人断言的不同，这与Greenplum无关.它与PostgreSQL8.2 息息相关.我验证了 anyarray 无法在 vanilla PostgreSQL 8.2 上转换为 text.
事实上，使 select ('{a,b,c}'::text[])::text; 工作的更改是在 8.3.0 版中引入的，根据以下更改日志(在随源分发的 HISTORY 文件中):
* 创建一个通用机制，支持从*每个*数据类型的标准字符串类型(TEXT、VARCHAR、CHAR)，通过调用数据类型的 I/O 函数 (Tom)以前，此类强制转换仅适用于具有专门用于此目的的功能.这些新演员是仅在字符串方向赋值，仅在字符串方向显式赋值另一个方向，因此应该不会令人惊讶行为.为了让我 100% 确定这一点，我只是继续从源代码编译 pg 8.2.23 和 8.3.0.确实，在 8.3.0 中它有效:
test=# select version();版本------------------------------------------------------------------------------------------------------------x86_64-unknown-linux-gnu 上的 PostgreSQL 8.3.0，由 GCC gcc47 (GCC) 4.7.2 20121109 (Red Hat 4.7.2-8) 编译(1 行)test=# select ('{a,b,c}'::text[])::text;文本---------{a,b,c}(1 行)但不是在 8.2.23 上:
test=# select version();版本-------------------------------------------------------------------------------------------------------------x86_64-unknown-linux-gnu 上的 PostgreSQL 8.2.23，由 GCC gcc47 (GCC) 4.7.2 20121109 (Red Hat 4.7.2-8) 编译(1 行)test=# select ('{a,b,c}'::text[])::text;错误:无法将类型 text[] 转换为 text第 1 行:选择 ('{a,b,c}'::text[])::text;如果我不明白你的问题:但是下面是一种进行文本到数组和数组到文本转换的方法
array_to_string(anyarray, text)
示例:postgres=# select * from array_to_string(array[1,2,3],'');数组到字符串-----------------123(1 行)string_to_array(text, text) :
postgres=# select string_to_array('xx~^~yy~^~zz', '~^~');字符串到数组-----------------{xx,yy,zz}(1 行)如果您想阅读有关数组函数的更多信息，请查看:http://www.postgresql.org/docs/8.2/static/functions-array.html
更新 #1:对于多维数组:
创建或替换函数aaa(anyarray,text)返回 SETOF 文本语言 plpgsqlAS $函数$DECLARE s $1%type;开始FOREACH 的 SLICE 1 IN ARRAY $1 循环RETURN NEXT array_to_string(s,$2);结束循环；返回;结尾;$函数$;postgres=# select aaa('{{a,b,c},{x,y,z}}'::text[], ',');啊啊啊啊-------一、二、三x,y,z(2 行)postgres=# select aaa('{a,b,c}'::text[], ',');啊啊啊啊-------一、二、三(1 行)In PostgreSQL8.2, what's the inverse of PostgreSQL's text-to-array conversion?
As we know:
select '{a,b,c}'::text[] as x, '{{0,1},{2,3}}'::int[][] as y;
/*
* x | y
* ---------+---------------
* {a,b,c} | {{0,1},{2,3}}
*/
Is there a predefined function f(anyarray) or an operator to do the inverse?
By "inverse", I mean that applying f(x)::text[] or f(y)::int[][] would bring it back to array form.
The following is a hypothetical example, just to illustrate the point.
select 'x='||f(x) as x_str, 'y'=f(y) as y_str
from (
select '{a,b,c}'::text[] as x, '{{0,1},{2,3}}'::int[][] as y;
) as a;
/*
* x | y
* -----------+-----------------
* x={a,b,c} | y={{0,1},{2,3}}
*/
Edit: Unfortunately (and trust me, that's the first thing I had tried before wasting anyone's time here), select x::text doesn't work for me (because I'm stuck with PostgreSQL8.2 --I use Greenplum):
test=> SELECT ('{a,b,c}'::text[])::text;
ERROR: cannot cast type text[] to text
LINE 1: SELECT ('{a,b,c}'::text[])::text;
^
Edit #2: Unlike what has been asserted by some, this has nothing to do with Greenplum. It has everything to do with PostgreSQL8.2. I verified that anyarray cannot be cast to text on a vanilla PostgreSQL 8.2.
In fact, the change that makes select ('{a,b,c}'::text[])::text; work was introduced in version 8.3.0, as per the following change log (in the HISTORY file distributed with the source):
* Create a general mechanism that supports casts to and from the standard string types (TEXT, VARCHAR, CHAR) for *every* datatype, by invoking the datatype's I/O functions (Tom) Previously, such casts were available only for types that had specialized function(s) for the purpose. These new casts are assignment-only in the to-string direction, explicit-only in the other direction, and therefore should create no surprising behavior.
In order for me to be 100% certain about this, I just went ahead and compiled from source both pg 8.2.23 and 8.3.0. Indeed, in 8.3.0 it works:
test=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.3.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc47 (GCC) 4.7.2 20121109 (Red Hat 4.7.2-8)
(1 row)
test=# select ('{a,b,c}'::text[])::text;
text
---------
{a,b,c}
(1 row)
But not on 8.2.23:
test=# select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.23 on x86_64-unknown-linux-gnu, compiled by GCC gcc47 (GCC) 4.7.2 20121109 (Red Hat 4.7.2-8)
(1 row)
test=# select ('{a,b,c}'::text[])::text;
ERROR: cannot cast type text[] to text
LINE 1: select ('{a,b,c}'::text[])::text;
If I don't understand your question clearly : but following is a way to do text to array and array to text conversion
array_to_string(anyarray, text)
Example:
postgres=# select * from array_to_string(array[1,2,3],'');
array_to_string
-----------------
123
(1 row)
string_to_array(text, text) :
postgres=# select string_to_array('xx~^~yy~^~zz', '~^~');
string_to_array
-----------------
{xx,yy,zz}
(1 row)
if you want to read more about array functions have a look: http://www.postgresql.org/docs/8.2/static/functions-array.html
Update #1: For multi dimensional array:
CREATE OR REPLACE FUNCTION aaa(anyarray,text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE s $1%type;
BEGIN
FOREACH s SLICE 1 IN ARRAY $1 LOOP
RETURN NEXT array_to_string(s,$2);
END LOOP;
RETURN;
END;
$function$;
postgres=# select aaa('{{a,b,c},{x,y,z}}'::text[], ',');
aaa
-------
a,b,c
x,y,z
(2 rows)
postgres=# select aaa('{a,b,c}'::text[], ',');
aaa
-------
a,b,c
(1 row)
这篇关于PostgreSQL 8.2 -- 如何获得任何数组的字符串表示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
