0
create or replace function help_constraint(p_schemaname text, p_tablename text) 
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text)
as 
$body$
  SELECT 
 case when substr(tc.constraint_name, 1, 3) = 'ck_' and tc.constraint_type = 'CHECK' then 'CHECK Table level '
when tc.constraint_type = 'CHECK' then 'CHECK on column ' || ccu.column_name
when tc.constraint_type = 'PRIMARY KEY' then 'PRIMARY KEY (clustered)' 
 when tc.constraint_type = 'DEFAULT' then 'DEFAULT on column ' || ccu.column_name
else '' end AS constraint_type,
tc.constraint_name,null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,coalesce(cc.check_clause, ccu.column_name) as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,ccu.column_name
from information_schema.table_constraints AS tc
left JOIN information_schema.constraint_column_usage ccu ON tc.constraint_catalog = ccu.constraint_catalog 
 AND tc.constraint_schema = ccu.constraint_schema 
 AND tc.constraint_name = ccu.constraint_name
left JOIN information_schema.check_constraints cc ON  tc.constraint_catalog = cc.constraint_catalog 
 AND tc.constraint_schema = cc.constraint_schema 
 AND tc.constraint_name = cc.constraint_name
WHERE lower(tc.constraint_type) in ('foreign key','primary key','check')
and lower(ccu.table_schema) = 'compare' and lower(ccu.table_name) = 'testpostgresql'
union all
SELECT 'DEFAULT on column ' || cc.column_name,
'DF_' || cc.column_name as constraint_name,null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,column_default as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,cc.column_name
FROM information_schema.columns cc
WHERE (table_schema, table_name) = ('p_schemaname', 'p_tablename')
and column_default is not null;
$body$
language sql;

ERROR: return type mismatch in function declared to return record DETAIL: Final statement returns text instead of boolean at column 5. CONTEXT: SQL function "help_constraint"

  • So what is the expected output? Are you looking for returns table (...)? Also there is no need for dynamic SQL or concatenating the parameters into a SQL string. – a_horse_with_no_name Aug 22 '18 at 10:24
  • 1
    You specify returns char - how do you want to obtain multiple rows? – Akina Aug 22 '18 at 10:25
  • expected output should be in table format(rows*column)...just like if we run this query manually...i'm looking for select help_constraint('schemaname','tablename') should display me all columns constraints from the query in function as pasted above...in tabular format...currently it is only giving me one row PGMIDMSPGSQL=> select help_constraint('calcspg','testpostgresql'); help_constraint ---------------- CHECK 1 row) – rock_techie Aug 22 '18 at 10:31
1

It seems you simply want to return the result of the query. To do that, the function needs to be declared as a set returning function. Your current function is a scalar function that returns a single character (char).

You also don't need dynamic SQL or even PL/pgSQL for that:

create or replace function help_constraint(p_schemaname text, p_tablename text) 
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text)
as 
$body$
  SELECT tc.constraint_type,
         tc.constraint_name,
         null::text as delete_action,
         null::text as update_action,
         null::boolean as status_enabled,
         null::text as status_for_replication,
         coalesce(cc.check_clause, ccu.column_name ) as constraint_keys 
  from information_schema.table_constraints AS tc 
    JOIN information_schema.constraint_column_usage ccu 
           ON tc.constraint_catalog = ccu.constraint_catalog 
          AND tc.constraint_schema = ccu.constraint_schema 
          AND tc.constraint_name = ccu.constraint_name
    left JOIN information_schema.check_constraints cc 
           ON tc.constraint_catalog = cc.constraint_catalog 
          AND tc.constraint_schema = cc.constraint_schema 
          AND tc.constraint_name = cc.constraint_name 
  where tc.constraint_type IN ('FOREIGN KEY', 'PRIMARY KEY', 'CHECK')
    and ccu.table_schema = p_schemaname 
    and ccu.table_name = p_tablename;
$body$
language sql; 

Note that I change the first left join to an inner join as the where condition on ccu makes it an inner join anyways.

To use that function use:

select *
from help_constraints('public', 'my_table');
0

I got the final answer and code written now...for anyone else benefit

create or replace function help_constraint(p_schemaname text, p_tablename text) 
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text,data_compression text,default_uid text,partition_qty text,column_name text)
as 
$body$
  SELECT case when substr(tc.constraint_name, 1, 3) = 'ck_' and tc.constraint_type = 'CHECK' then 'CHECK Table level '
when tc.constraint_type = 'CHECK' then 'CHECK on column ' || ccu.column_name
when tc.constraint_type = 'PRIMARY KEY' then 'PRIMARY KEY (clustered)' 
 when tc.constraint_type = 'DEFAULT' then 'DEFAULT on column ' || ccu.column_name
else '' end AS constraint_type,
         tc.constraint_name,
         null::text as delete_action,
         null::text as update_action,
         null::boolean as status_enabled,
         null::text as status_for_replication,
         coalesce(cc.check_clause, ccu.column_name ) as constraint_keys,
         null::text as data_compression,
         null::text as default_uid,
         null::text as partition_qty,
         ccu.column_name         
  from information_schema.table_constraints AS tc 
    JOIN information_schema.constraint_column_usage ccu 
           ON tc.constraint_catalog = ccu.constraint_catalog 
          AND tc.constraint_schema = ccu.constraint_schema 
          AND tc.constraint_name = ccu.constraint_name
    left JOIN information_schema.check_constraints cc 
           ON tc.constraint_catalog = cc.constraint_catalog 
          AND tc.constraint_schema = cc.constraint_schema 
          AND tc.constraint_name = cc.constraint_name 
  where tc.constraint_type IN ('FOREIGN KEY', 'PRIMARY KEY', 'CHECK')
    and ccu.table_schema = p_schemaname 
    and ccu.table_name = p_tablename
    union all
SELECT 'DEFAULT on column ' || cc.column_name,
'DF_' || cc.column_name as constraint_name,

null as delete_action,null as update_action,null as status_enabled,
null as status_for_replication,column_default as constraint_keys,null as data_compression,null as default_uid,null as partition_qty,cc.column_name
FROM information_schema.columns cc
WHERE (table_schema, table_name) = ('schemaname', 'tablename')
and column_default is not null;
$body$
language sql; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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