Check column result size in functions
От | Marcos Pegoraro |
---|---|
Тема | Check column result size in functions |
Дата | |
Msg-id | CAB-JLwZd2-Vr2HzR-Cu4VQCOg5H1Xwme+xx4SZUpn5wZcM2OAA@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Check column result size in functions
|
Список | pgsql-general |
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();
$$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
В списке pgsql-general по дате отправления: