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

Popular posts from this blog

google chrome - Developer tools - How to inspect the elements which are added momentarily (by JQuery)? -

angularjs - Showing an empty as first option in select tag -

php - Cloud9 cloud IDE and CakePHP -