MogDB支持cardinality函数吗？

我来答

2022-06-20

从postgresql迁移到mogdb，有替代的函数吗？

### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist
### Cause: org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist
### Cause: org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist
Caused by: org.postgresql.util.PSQLException: ERROR: function cardinality(character varying[]) does not exist

我来答

添加附件

问题补充

1条回答

默认

最新

DarkAthena

mogdb官方文档说支持cardinality函数，但实测截止到3.0.0版本，一致都不支持，openGausss也不支持。
https://docs.mogdb.io/zh/mogdb/v2.0.1/13.0-array-types#访问数组

文档问题已反馈，目前暂时可通过count结合unnest来查询，比如

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
    
SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';
---改写
select count(1) from ( SELECT unnest(schedule) FROM sal_emp WHERE name = 'Carol');

或者使用自定义函数

create or replace function cardinality(anyarray) return int4 is
l_ct int4;
begin 
	select count(1) into l_ct from  unnest($1);
return l_ct;
end;

有用 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏