Function Returning SETOF RECORD: Trouble With Char Type

["Angus B. Atkins-Trimnell" <trimnell@xxxxxxx>]

Hello,

I am having trouble with a function designed to return all column 
constraints on a table.  The problem is not in the SQL, which works fine 
on its own.  The problem comes when the function is invoked using SELECT 
* FROM function_name(arg_name) AS temp_table(.....).  When the function 
does not contain either of the two char columns, it returns perfectly 
well, but when either char column is added, it returns "ERROR: wrong 
record type supplied in RETURN NEXT".  I'm hoping this is something 
obvious or know that I am missing.  Working and non-working code are 
below.  Please pardon the complex query.

Thanks.

--Angus Atkins-Trimnell

Begin Working Code:
**********************************

CREATE OR REPLACE FUNCTION sql_get_schema_full(select_table text) 
RETURNS SETOF RECORD AS $$
DECLARE
 sql_result record;
BEGIN
 FOR sql_result in EXECUTE
   'SELECT t.relname, a.attname, c.conname, c.consrc, ft.relname, 
fa.attname FROM (((pg_class AS t LEFT JOIN
   (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) 
AS a ON t.oid=a.attrelid) LEFT JOIN
   pg_constraint AS c ON t.oid=c.conrelid AND a.attnum=ANY(c.conkey)) 
LEFT JOIN
   pg_class AS ft ON ft.oid=c.confrelid) LEFT JOIN
   (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) 
AS fa ON ft.oid=fa.attrelid AND fa.attnum=ANY(c.confkey)
   WHERE t.relname=''' || $1 || '''' LOOP
   RETURN NEXT sql_result;
 END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * FROM sql_get_schema_full('temp_visit') AS temp_schema(table1 
name, column1 name, constname name, consttext text, table2 name, column2 
name);

This works fine.
************************************
End Working Code

Begin Failing Code
************************************

CREATE OR REPLACE FUNCTION sql_get_schema_full(select_table text) 
RETURNS SETOF RECORD AS $$
DECLARE
 sql_result record;
BEGIN
 FOR sql_result in EXECUTE
   'SELECT t.relname, a.attname, c.conname, c.contype, c.consrc, 
ft.relname, fa.attname FROM (((pg_class AS t LEFT JOIN (SELECT attname, 
attnum, attrelid FROM pg_attribute WHERE attnum>0) AS a ON 
t.oid=a.attrelid) LEFT JOIN
   pg_constraint AS c ON t.oid=c.conrelid AND a.attnum=ANY(c.conkey)) 
LEFT JOIN
   pg_class AS ft ON ft.oid=c.confrelid) LEFT JOIN
   (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) 
AS fa ON ft.oid=fa.attrelid AND fa.attnum=ANY(c.confkey)
   WHERE t.relname=''' || $1 || '''' LOOP
   RETURN NEXT sql_result;
 END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT * FROM sql_get_schema_full('temp_visit') AS temp_schema(table1 
name, column1 name, constname name,
consttype char, consttext text, table2 name, column2 name);

ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "sql_get_schema_full" line 11 at return next

**********************************
End Failing Code

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general