Postgresql: Using an index type as a data type?

Question

i'm trying to extract from a row the fields that are part of the primary index of the table. (as a record)

Example, if I create a table like this:

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');

Then i can do :

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

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

...or i can do...

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

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

But, i want to do :

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

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

... or ...

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

But, problem:

ERROR:  type "t1_pkey" does not exist

This type exists probably somewhere because :

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

Any solution ?

t1 is not a type. It's the name of a table, you can however use a table name as a composite row/record type. It would be very strange if you cannot do this with a primary key definition since the database spend its whole day comparing and sorting primary keys... — mathieu
– mathieu, Commented Mar 29, 2015 at 13:02
From a deleted answer's comment, matthieu wrote: "I dont want the type of the indexed columns, i want the composite type of the primary key" — Craig Ringer
– Craig Ringer, Commented Mar 30, 2015 at 0:00

mathieu · Accepted Answer · 2015-03-29 13:05:01Z

1

Just to make clear what I want to achieve, thats the temporary solution I found. It's ugly but hey, it works...

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)

edited Mar 29, 2015 at 13:05

answered Mar 29, 2015 at 12:57

mathieu

4773 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

You can prettify that a little by constructing the SELECT using string concatenation so it returns all the desired fields at once. But the general idea is about right, you're unlikely to find a method much simpler than this. I recommend using information_schema rather than direct pg_catalog access so you're protected a bit against version-specific changes, but that's about it.

Collectives™ on Stack Overflow

Postgresql: Using an index type as a data type?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related