Postgresql: Using an index type as a data type? -
i'm trying extract row fields part of primary index of table. (as record)
example, if create table this:
create table t1 (k1 int not null, k2 int not null, label text, primary key(k1, k2)); insert t1(k1,k2,label) values (3,5,'hello');
then can :
select * json_populate_record(null::t1, '{}'); k1 | k2 | label ----+----+------- | | (1 row)
...or can do...
select row_to_json(row) (select * t1) row; row_to_json --------------------------------- {"k1":3,"k2":5,"label":"hello"} (1 row)
but, want :
select * json_populate_record(null::t1_pkey, '{}'); k1 | k2 | ----+----+ | | (1 row)
... or ...
select row_to_json(row::t1_pkey) (select * t1) row; row_to_json --------------------------------- {"k1":3,"k2":5} (1 row)
but, problem:
error: type "t1_pkey" not exist
this type exists somewhere because :
\d t1_pkey index "public.t1_pkey" column | type | definition --------+---------+------------ k1 | integer | k1 k2 | integer | k2 primary key, btree, table "public.t1"
any solution ?
just make clear want achieve, thats temporary solution found. it's ugly hey, works...
create or replace function public.pka(in t_oid oid, in t_row anyelement) returns record $$ declare k text; v text; keys text[]; sel text[]; int; rec record; begin select array(select a.attname pg_index join pg_attribute on a.attrelid = i.indrelid , a.attnum = any(i.indkey) i.indisprimary , i.indrelid = t_oid) keys; := 0; foreach k in array keys loop := + 1; execute format('select $1.%s', k) using t_row v; sel[i] := concat(quote_literal(v),' ',k); end loop; execute format('select %s', array_to_string(sel, ', '), sel) rec; return rec; end; $$ language 'plpgsql' stable; select to_json(pka('t1'::regclass::oid, row::t1)) (select * t1) row; to_json --------------------- {"k1":"3","k2":"5"} (1 row)
Comments
Post a Comment