I'm trying to write a PL/pgSQL function with a VARCHAR argument, which should be limited. Consider following function, with a VARCHAR parameter limited to a size of 5 characters:
CREATE OR REPLACE FUNCTION do_nothin(v_value VARCHAR(5))
RETURNS TEXT AS
$$
DECLARE
BEGIN
RETURN v_value;
END;
$$
LANGUAGE plpgsql;
When I call this function with a text bigger than 5, it does work:
DO $$
DECLARE
BEGIN
PERFORM do_nothin('123456');
END;
$$
It gives me the result '123456', but why? This should give me an error right?
If I define a varchar like this:
DO $$
DECLARE
v_mytext VARCHAR(5);
BEGIN
v_mytext := '123456';
END;
$$
it gives me an error as expected:
ERROR: value too long for type character varying(5) CONTEXT: PL/pgSQL function inline_code_block line 5 at assignment SQL state: 22001
I want this error thrown when there is a too big argument for the function, but how?
From historical reasons PostgreSQL ignore size modificator in function argument type (typmod). There are some issues related to implementation and simple and good enough solution is ignore this type feature - the expected behave is simple with varchar, but can be pretty difficult to design correct and user friendly behave for numeric types.
create or replace function fx(a varchar(10))
returns varchar as $$ begin return a; end $$ language plpgsql;
postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx(a character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$ begin return a; end $function$
You can see - typmod value is not persistent - and then has not effect.
If you really need some limits, then you should to use domain type:
postgres=# create domain varchar_10 as varchar(10);
CREATE DOMAIN
postgres=# create or replace function fx2(a varchar_10)
returns varchar as $$ begin return a; end $$ language plpgsql
CREATE FUNCTION
postgres=# select fx2('12345678901');
ERROR: value too long for type character varying(10)
Domains are persistent type synonyms with all (typmods, checks).