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?
Best Answer
Your function ..
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:If the first column of that type (table) is defined
varchar(8)
(with length modifier) - as the error message indicates, you have to returnvarchar(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)
andvarchar(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:
Two basic ways to fix your problem:
You can cast the returned values to match the definition of
patient_for_registrar
:Or you can change the
RETURNS
clause. I would useRETURNS TABLE
and declare a matching composite type. Here is an example.As an aside: I never use
varchar
if I can avoid it - especially not with length modifier. It offers hardly anything that the typetext
couldn't do. If I need a length restriction, I use a column constraint which can be changed without rewriting the whole table.