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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
