Check column result size in functions

Started by Marcos Pegoraroover 2 years ago2 messagesgeneral
Jump to latest
#1Marcos Pegoraro
marcos@f10.com.br

My functions should return varchar(5) or should return an exception, but
sometimes they return CHARACTER VARYING. Why ?

create or replace function f_sql() returns varchar(5) language sql as
$$select '0123456789'$$;
--works, but shouldn't
select * from f_sql();

create domain T5 as varchar(5);
create or replace function f_sql_domain() returns T5 language sql as
$$select '0123456789'$$;
--exception because of domain
--ERROR: value too long for type character varying(5)
select * from f_sql_domain();

create or replace function f_sql_table() returns table (V5 varchar(5))
language sql as
$$select '0123456789'$$;
--works, but shouldn't
select * from f_sql_table();

create or replace function f_plpgsql() returns varchar(5) language plpgsql
as
$$begin return '0123456789';end;$$;
--works, but shouldn't
select * from f_plpgsql();

create or replace function f_plpgsql_table() returns table(V5 varchar(5))
language plpgsql as
$$begin return query select '0123456789';end;$$;
--Shouldn't be this exception the response for all other calls ?
--ERROR: structure of query does not match function result type
select * from f_plpgsql_table();

I've created some functions using LANGUAGE SQL but strangely all varchar
results did not respect I've declared.
To be sure column result size is fine I have to use a DOMAIN ? Because even
on PLPGSQL, column size are checked only if RETURN TABLE.

thanks for any explanation

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Marcos Pegoraro (#1)
Re: Check column result size in functions

On Tue, Dec 12, 2023 at 3:13 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

My functions should return varchar(5) or should return an exception, but
sometimes they return CHARACTER VARYING. Why ?

thanks for any explanation

The observed behavior is documented.

https://www.postgresql.org/docs/current/sql-createfunction.html (see notes)

David J.