Cast produces 'Returned type character varying does not match expected type character varying(8)'
database, plpgsql, postgresql, types, varchar
Solution
Your function ..
RETURNS SETOF patient_for_registrar
The returned row type must match the declared type exactly. You did not disclose the definition of `patient_for_registrar`, probably the associated composite type of a table. I quote the manual about Declaration of Composite Types:
Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.
If the first column of that type (table) is defined `varchar(8)` (with length modifier) - as the error message indicates, you have to return `varchar(8)` with the same length modifier; `varchar` won't do. It is irrelevant for that matter whether the string length is only 8 characters, the data type has to match.
`varchar`, `varchar(n)` and `varchar(m)` are different data types for PostgreSQL.
Older versions did not enforce the type modifiers, but with PostgreSQL 9.0 this was changed for plpgsql:
PL/pgSQL now requires columns of composite results to match the expected type modifier as well as base type (Pavel Stehule, Tom Lane)
For example, if a column of the result type is declared as NUMERIC(30,2), it is no longer acceptable to return a NUMERIC of some other precision in that column. Previous versions neglected to check the type modifier and would thus allow result rows that didn't actually conform to the declared restrictions.
Two basic ways to fix your problem:
You can cast the returned values to match the definition of `patient_for_registrar`:
nfa.patient_id_number::varchar(8)
Or you can change the `RETURNS` clause. I would use `RETURNS TABLE` and declare a matching composite type. Here is an example.
RETURNS TABLE (patient_for_registrar varchar, col2 some_type, ...)
As an aside: I never use `varchar` if I can avoid it - especially not with length modifier. It offers hardly anything that the type `text` couldn't do. If I need a length restriction, I use a column constraint which can be changed without rewriting the whole table.
Problem
Yesterday we had a PostgreSQL database upgraded to version 9.1.3. We thought we had everything tested and ready, but there is a function we missed. It returns a table type like this: ``` CREATE OR REPLACE FUNCTION myfunc( patient_number varchar, tumor_number_param varchar, facility_number varchar ) RETURNS SETOF patient_for_registrar LANGUAGE plpgsql AS $body$ BEGIN RETURN QUERY SELECT cast(nfa.patient_id_number as varchar), ... ``` I only only give the first column of the select because that is where the error happens. Before today this function ran fine, but now it gives this error: ERROR: structure of query does not match function result type Detail: Returned type character varying does not match expected type character varying(8) in column 1. Where: PL/pgSQL function "getwebregistrarpatient_withdeletes" line 3 at RETURN QUERY [SQL State=42804] The column `nfa.patient_id_number` is text and is being cast for the column `patient_id_number` in `patient_for_registrar` that is `varchar(8)`. After reading about this some I think the problem is because the column length isn't being specified when casting from text. But the problem is I've tried various combinations of substrings to fix this and none are solving the problem: ``` substring(cast(nfa.patient_id_number as varchar) from 1 for 8), cast(substring(nfa.patient_id_number from 1 for 8) as varchar), cast(substring(nfa.patient_id_number from 1 for 8) as varchar(8)), ``` Does anyone have any pointers?