Returned data type information_schema.identifier does not match expected type

Question

I am getting this error:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type information_schema.sql_identifier does not match expected type character varying in column 1.
CONTEXT:  PL/pgSQL function app.get_custom_task_fields(integer,character varying,integer) line 10 at RETURN QUERY

To fix it, I need to know the type of column_name, ordinal_position and data_type in my query. Or more generally, what is the data type of columns in information_schema.columns and how do I convert sql_identifier to an 'outputable' format to get it out of my function?

This is my function:

CREATE OR REPLACE FUNCTION app.get_custom_task_fields(sess_identity_id int
                                                     ,session_code_str varchar
                                                     ,sess_company_id int)
RETURNS TABLE(field_name varchar,ordinal_position integer,field_type varchar) 
AS $$
DECLARE

BEGIN

    RETURN QUERY
    SELECT t.column_name,t.ordinal_position,t.data_type
    FROM INFORMATION_SCHEMA.COLUMNS as t
    WHERE table_name = 'task_custom' order by t.ordinal_position;
END;
$$ LANGUAGE PLPGSQL;

Answer 1

Question 1

What is the data type of columns in information_schema.columns?

You can either look it up in the manual:

• https://www.postgresql.org/docs/current/static/information-schema.html

Or you can ask Postgres directly (using the catalog table pg_attribute):

SELECT attname, atttypid::regtype
FROM   pg_attribute
WHERE  attrelid = 'information_schema.columns'::regclass
ORDER  BY attnum;

 attname       | atttypid
---------------+----------------------------------
 table_catalog | information_schema.sql_identifier
 table_schema  | information_schema.sql_identifier
 table_name    | information_schema.sql_identifier
...

Question 2

And how do I convert sql_identifier to an 'outputable' format to get it out of my function?

To find out specifics of any data type:

SELECT typname, typtype  -- 'd' is for 'domain'
     , typbasetype::regtype
FROM   pg_type
WHERE  oid = 'information_schema.sql_identifier'::regtype;

 typname        | typtype | typbasetype
----------------+---------+-----------
 sql_identifier | d       | character varying

So the data type information_schema.sql_identifier is a DOMAIN on varchar. To find out possible casts:

SELECT casttarget::regtype, castcontext
FROM   pg_cast
WHERE  castsource = 'character varying'::regtype;

 casttarget | castcontext
------------+------------
 regclass   | i
 text       | i
 character  | i
 ...

You can cast to a desired output type. But there is a ...

Simpler solution

You don't need to know any of this. Just reference the data type of columns. The manual about CREATE FUNCTION

The type of a column is referenced by writing table_name.column_name%TYPE.

Write your function like this and you can't go wrong:

CREATE OR REPLACE FUNCTION app.get_custom_task_fields(sess_identity_id int
                                                    , session_code_str varchar
                                                    , sess_company_id int)
  RETURNS TABLE(field_name       information_schema.columns.column_name%TYPE
              , ordinal_position information_schema.columns.ordinal_position%TYPE
              , field_type       information_schema.columns.data_type%TYPE) AS
$FUNC$
BEGIN
   RETURN QUERY
   SELECT t.column_name, t.ordinal_position, t.data_type
   FROM   information_schema.columns t
   WHERE  t.table_name = 'task_custom'
   ORDER  BY t.ordinal_position;
END
$FUNC$  LANGUAGE plpgsql;

The column reference is converted to the underlying type at function creation time. You'll see notices informing you about that.

Answer 2

Your easiest (and probably, safest) solution is to CAST the columns to the types you wish to return (using the :: operator, or the CAST(xx AS type) function):

CREATE OR REPLACE FUNCTION get_custom_task_fields(sess_identity_id int,session_code_str varchar,sess_company_id int)
RETURNS TABLE(field_name varchar,ordinal_position integer,field_type varchar) 
AS $$
BEGIN
    RETURN QUERY 
        SELECT t.column_name    :: varchar
           , t.ordinal_position :: integer
           , t.data_type        :: varchar
        FROM INFORMATION_SCHEMA.COLUMNS as t 
        WHERE table_name = 'task_custom' 
        ORDER BY t.ordinal_position;
END;
$$ LANGUAGE PLPGSQL;