PLPGSQL function(multiple column output)--gives error

Question

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"

• user1822: 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.
• Akina: You specify returns char - how do you want to obtain multiple rows?
• rock_techie: 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)

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

Answer 2

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;