arrays - postgres:使用交叉表或任何 json 函数构建选择查询
问题描述
create table foo (i int, j text);
select table_schema, table_name, column_name , ordinal_position
from information_schema.columns where table_name = 'foo';
Output
table_schema table_name column_name ordinal_position
public foo i 1
public foo j 2
通过使用第二条语句,我需要形成一个选择查询select i, j from public.foo
。一个简单的函数就足够了,如果我们传递一个表名,结果可以是 select 语句的字符串
解决方案
您可以使用聚合:
select concat_ws(' ',
'select',
string_agg(column_name, ', ' order by ordinal_position),
'from',
table_schema || '.' || table_name
) q
from information_schema.columns
where table_name = 'foo'
group by table_schema, table_name
或者没有group by
子句:
select concat_ws(' ',
'select',
string_agg(column_name, ', ' order by ordinal_position),
'from',
max(table_schema) || '.' || max(table_name)
) q
from information_schema.columns
where table_name = 'foo' and table_schema = 'public'
| 问 | | :---------------------------- | | 从 public.foo 中选择 i, j |
推荐阅读
- python - calculate difference in value between rows across group in python
- r - Determine length of sequences between columns or in string - and paste result
- python - FuzzyWuzzy python, matching two identical phrases in to diverse scripts brought diverse result
- reactjs - Filepond : manually revert to default image
- r - Replace special characters in a dataframe with the normal variant dataframe is empty
- ruby-on-rails - Rails has_many unique scope
- html - 创建带有字体真棒图标的响应式卡片
- database - 使用 Flyway 迁移创建用户
- excel - 通过宏插入表单控制按钮时出现VBA Excel“预期函数或变量”错误
- php - php中的求和数组