Обсуждение: Function Returning SETOF RECORD: Trouble With Char Type

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

"Angus B. Atkins-Trimnell" <trimnell@uic.edu> writes:
> I am having trouble with a function designed to return all column
> constraints on a table.

I think the problem is that you're declaring the contype return column
as char (ie, character(1)) when pg_constraint.contype is actually "char"
(a historical PG datatype that's really just a single byte).  The naming
confusion is unfortunate but we've never bitten the bullet to rename
"char" to something else.

Use quotes, or explicitly cast the catalog column to regular char
in the function's query.

            regards, tom lane

Thanks a lot.  I missed your post when it first came up, but I just tried double-quoting the type indicator and it worked like a charm!

--Angus

Tom Lane wrote:

"Angus B. Atkins-Trimnell" <trimnell@uic.edu> writes: 

I am having trouble with a function designed to return all column 
constraints on a table.   

I think the problem is that you're declaring the contype return column
as char (ie, character(1)) when pg_constraint.contype is actually "char"
(a historical PG datatype that's really just a single byte).  The naming
confusion is unfortunate but we've never bitten the bullet to rename
"char" to something else.

Use quotes, or explicitly cast the catalog column to regular char
in the function's query.
		regards, tom lane