PostgreSQL PLPGSQL – Function with Multiple Column Output Error

plpgsqlpostgresql

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"

Best Answer

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