-2
create or replace function help_index(t_nspname text, t_tablename text) 
  returns table(index_id text,is_disabled text,index_name text, index_description text, index_keys text, included_columns text, filter_definition text,columns_in_tree text,columns_in_leaf text,index_type text)
as 
$body$
SELECT
  null::text as index_id,
  null::text as is_disabled,
  i.relname::text AS index_name,
  null::text as  index_description,
       ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS index_keys,
  null::text as included_columns,
  null::text as filter_definiton,
ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS columns_in_tree,
ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS columns_in_leaf,
  am.amname                AS index_type
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
  JOIN pg_user AS U ON i.relowner = U.usesysid
WHERE NOT nspname LIKE 'pg%' -- Excluding system tables
AND ns.nspname=t_nspname
AND i.relname=t_tablename;
$body$
language sql; 

ERROR: syntax error at or near "leaf" LINE 1: leaf text,index_type text)

Now got this error..looks like need to add typecast for index_keys 5th column but how?

closed as unclear what you're asking by Colin 't Hart, hot2use, Erik Darling, Max Vernon, Md Haidar Ali Khan Sep 5 '18 at 6:56

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

3

SQL functions are a bit more picky about data types than "regular SQL".

If you look at the definition of pg_class you will see that relname is defined as type name, not text.

The error message

Final statement returns name instead of text at column 3.

is telling you exactly that. So you need to cast that column to text:

i.relname::text AS index_name,

The same is true for the column 10 am.amname


Edit after the question was changed.

The new error message tells you (again), exactly what your problem is:

Final statement returns text[] instead of text at column 5.

The select expression for column 5 (and 6) is:

 ARRAY( ... ) 

So that returns an array, not a string (text).

As you you seem to want to get a list of columns for that index not an array, you need to use string_agg():

  (select string_agg(pg_get_indexdef(idx.indexrelid, colidx + 1, true), ',')
   from generate_subscripts(idx.indkey,1) as t(colidx)) as index_keys,

Your condition i.relname=t_tablename is also wrong as i.relname is the name of the index, not the name of the table you want to get the indexes for.

You need to change the joins to something like this:

FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_user AS U ON i.relowner = U.usesysid
  JOIN pg_class AS tbl on tbl.oid = idx.indrelid --<< here
  JOIN pg_namespace AS ns ON tbl.relnamespace = ns.oid --<< here

Putting all that together, the following should do what you want:

create or replace function help_index(t_nspname text, t_tablename text) 
  returns table(index_id text,is_disabled text,index_name text, index_description text, index_keys text, included_columns text, filter_definition text,columns_in_tree text,columns_in_leaf text,index_type text)
as 
$body$
SELECT
    null::text as index_id,
    null::text as is_disabled,
    i.relname::text AS index_name,
    null::text as  index_description,
    (select string_agg(pg_get_indexdef(idx.indexrelid, colidx + 1, true), ',')
     from generate_subscripts(idx.indkey,1) as t(colidx)) as index_keys,
    null::text as included_columns,
    null::text as filter_definiton,
    (select string_agg(pg_get_indexdef(idx.indexrelid, colidx + 1, true), ',')
     from generate_subscripts(idx.indkey,1) as t(colidx)) as columns_in_tree,
    (select string_agg(pg_get_indexdef(idx.indexrelid, colidx + 1, true), ',')
     from generate_subscripts(idx.indkey,1) as t(colidx)) as columns_in_leaf,
    am.amname::text                AS index_type
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_user AS U ON i.relowner = U.usesysid
  JOIN pg_class AS tbl on tbl.oid = idx.indrelid
  JOIN pg_namespace AS ns ON tbl.relnamespace = ns.oid
WHERE NOT nspname LIKE 'pg%' -- Excluding system tables
  AND ns.nspname = t_nspname
  AND tbl.relname = t_tablename;
$body$
language sql; 
  • @rock_techie: note that with the upcoming v11 you could populate included_columns because including "non-key" columns will be supported in that version – a_horse_with_no_name Aug 27 '18 at 9:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.