将选择查询的输出存储在 postgres 的一个数组中

发布于 2024-11-16 04:09:52 字数 280 浏览 5 评论 0原文

我的代码是:

SELECT column_name
FROM information.SCHEMA.columns
WHERE table_name = 'aean'

它返回表aean的列名称。
现在我已经声明了一个数组:

DECLARE colnames text[]

如何将 select 的输出存储在 colnames 数组中。
是否需要初始化colnames?

My code is:

SELECT column_name
FROM information.SCHEMA.columns
WHERE table_name = 'aean'

It returns column names of table aean.
Now I have declared an array:

DECLARE colnames text[]

How can I store select's output in colnames array.
Is there any need to initialize colnames?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

落在眉间の轻吻 2024-11-23 04:09:52

有两种方法。一种是聚合:

SELECT array_agg(column_name::TEXT)
FROM information.schema.columns
WHERE table_name = 'aean'

另一种是使用数组构造函数:

SELECT ARRAY(
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_name = 'aean'
)

我假设这是针对 plpgsql 的。在这种情况下,您可以这样分配它:

colnames := ARRAY(
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name='aean'
);

There are two ways. One is to aggregate:

SELECT array_agg(column_name::TEXT)
FROM information.schema.columns
WHERE table_name = 'aean'

The other is to use an array constructor:

SELECT ARRAY(
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_name = 'aean'
)

I'm presuming this is for plpgsql. In that case you can assign it like this:

colnames := ARRAY(
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name='aean'
);
行至春深 2024-11-23 04:09:52

我有完全相同的问题。只需对 Denis 给出的解决方案进行另一项工作修改即可(必须指定类型):

SELECT ARRAY(
SELECT column_name::text
FROM information_schema.columns
WHERE table_name='aean'
)

I had exactly the same problem. Just one more working modification of the solution given by Denis (the type must be specified):

SELECT ARRAY(
SELECT column_name::text
FROM information_schema.columns
WHERE table_name='aean'
)
隱形的亼 2024-11-23 04:09:52

常规:

SELECT post_id FROM posts WHERE(poster_name='John');

output: [
  {'post_id': 1},
  {'post_id': 2},
  {'post_id': 3},
]

使用 ARRAY_AGG

SELECT ARRAY_AGG(post_id) FROM posts WHERE(poster_name='John');

output: [
 {[1, 2, 3]}
]

Regular:

SELECT post_id FROM posts WHERE(poster_name='John');

output: [
  {'post_id': 1},
  {'post_id': 2},
  {'post_id': 3},
]

Using ARRAY_AGG:

SELECT ARRAY_AGG(post_id) FROM posts WHERE(poster_name='John');

output: [
 {[1, 2, 3]}
]
何处潇湘 2024-11-23 04:09:52

转换为数据类型“TEXT”将确保您的查询运行没有任何问题。
在plpgsql中,当我们分配给数组变量时,我们不需要使用类型转换。我的要求是获取特定表的所有列名称的 CSV。我在 plpgsql 中使用了以下代码。

Declare col_list varchar[]:=NULL;
cols varchar:=NULL;
Begin
    col_list := ARRAY(select t.name from frm_columns t where t.tname='emp_mstr');
    cols := array_to_string(col_list,',');
    return cols;
End;

Casting to the datatype "TEXT" will ensure that your queries will run without any problem.
In plpgsql when we assign to a array variable, we need not use the type casting. My requirement was to get a CSV of all the column names of a particular table. I'd used the following code in plpgsql.

Declare col_list varchar[]:=NULL;
cols varchar:=NULL;
Begin
    col_list := ARRAY(select t.name from frm_columns t where t.tname='emp_mstr');
    cols := array_to_string(col_list,',');
    return cols;
End;
娇柔作态 2024-11-23 04:09:52
CREATE OR REPLACE FUNCTION f_test_array(in _colname text)
returns text as $body$
DECLARE colnames text[];
begin
colnames := ARRAY(
    SELECT column_name FROM information_schema.columns WHERE table_name='customer'
);
    if exists(select _colname = any(colnames))
    then return format('%s it exits.', _colname);
    else return format('%s not exits.', _colname);
end if;
end
$body$
LANGUAGE plpgsql;   

检查该列是否存在。
关键点:如果存在(select _colname = any(colnames))
我们还可以使用string_agg
String_agg 用法:

CREATE OR REPLACE FUNCTION f_test_array1(in _colname text)
returns text as $body$
DECLARE colnames text;
begin
colnames := (SELECT string_agg(column_name,',') FROM information_schema.columns WHERE table_name='customer')::text;
if exists(select colnames ilike '%' || quote_literal(_colname) ||'%')
    then return format('column %s  exits.', _colname);
    else return format('column %s does not exits.', _colname);
end if;
end
$body$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION f_test_array(in _colname text)
returns text as $body$
DECLARE colnames text[];
begin
colnames := ARRAY(
    SELECT column_name FROM information_schema.columns WHERE table_name='customer'
);
    if exists(select _colname = any(colnames))
    then return format('%s it exits.', _colname);
    else return format('%s not exits.', _colname);
end if;
end
$body$
LANGUAGE plpgsql;   

check if the column exists or not.
Key point: if exists(select _colname = any(colnames))
We can also using string_agg
String_agg usage:

CREATE OR REPLACE FUNCTION f_test_array1(in _colname text)
returns text as $body$
DECLARE colnames text;
begin
colnames := (SELECT string_agg(column_name,',') FROM information_schema.columns WHERE table_name='customer')::text;
if exists(select colnames ilike '%' || quote_literal(_colname) ||'%')
    then return format('column %s  exits.', _colname);
    else return format('column %s does not exits.', _colname);
end if;
end
$body$
LANGUAGE plpgsql;
兮子 2024-11-23 04:09:52

在函数中声明 colnames text[] 数组,然后在 begin 中编写以下查询:

SELECT column_name into colname
FROM信息.SCHEMA.列
WHERE 表名 = 'aean'

declare colnames text[] array in function and then write following query in begin:

SELECT column_name into colnames
FROM information.SCHEMA.columns
WHERE table_name = 'aean'

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文