PL/pgSQL function with VARCHAR as Parameter

Question

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?

Pavel Stehule · Accepted Answer · 2017-11-25 08:20:30Z

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).

kilimanjaro · Accepted Answer · 2023-06-10 18:07:34Z

0

According to the documentation "type modifiers are discarded by CREATE FUNCTION".

answered Jun 10, 2023 at 18:07

kilimanjaro

1281 silver badge3 bronze badges

Collectives™ on Stack Overflow

PL/pgSQL function with VARCHAR as Parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related