gpt4 book ai didi

Postgresql:使用索引类型作为数据类型?

转载 作者:行者123 更新时间:2023-11-29 13:28:19 25 4
gpt4 key购买 nike

我正在尝试从一行中提取属于表主索引的字段。 (作为记录)

例如,如果我创建一个这样的表:

CREATE TABLE t1 (k1 int not null, k2 int not null, label text, PRIMARY KEY(k1, k2));
INSERT INTO t1(k1,k2,label) values (3,5,'hello');

然后我可以做:

SELECT * from json_populate_record(null::t1, '{}');  

k1 | k2 | label
----+----+-------
| |
(1 row)

...或者我可以...

select row_to_json(row) from (select * from t1) as row;

row_to_json
---------------------------------
{"k1":3,"k2":5,"label":"hello"}
(1 row)

但是,我想做的是:

SELECT * from json_populate_record(null::t1_pkey, '{}');  

k1 | k2 |
----+----+
| |
(1 row)

……或者……

select row_to_json(row::t1_pkey) from (select * from t1) as row;
row_to_json
---------------------------------
{"k1":3,"k2":5}
(1 row)

但是,问题:

ERROR:  type "t1_pkey" does not exist

这种类型可能存在于某个地方,因为:

\d t1_pkey
Index "public.t1_pkey"
Column | Type | Definition
--------+---------+------------
k1 | integer | k1
k2 | integer | k2
primary key, btree, for table "public.t1"

有什么解决办法吗?

最佳答案

只是为了弄清楚我想要实现的目标,这是我找到的临时解决方案。这很丑陋,但嘿,它有效......

CREATE OR REPLACE
FUNCTION public.pka(in t_oid oid, in t_row anyelement)
RETURNS RECORD
AS
$$
DECLARE
k text;
v text;
keys text[];
sel text[];
i int;
rec record;
BEGIN

SELECT array(SELECT a.attname
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE i.indisprimary AND i.indrelid = t_oid) INTO keys;

i := 0;
FOREACH k IN ARRAY keys
LOOP
i := i + 1;
EXECUTE format('SELECT $1.%s', k) USING t_row INTO v;
sel[i] := concat(quote_literal(v),' as ',k);
END LOOP;

EXECUTE format('SELECT %s', array_to_string(sel, ', '), sel) INTO rec;
return rec;
END;
$$
LANGUAGE 'plpgsql' STABLE;


select to_json(pka('t1'::regclass::oid, row::t1)) from (select * from t1) as row;
to_json
---------------------
{"k1":"3","k2":"5"}
(1 row)

关于Postgresql:使用索引类型作为数据类型?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29320784/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com