I have following simple code user defined function that want to format the string.

create or replace function func_format_usage(v1 integer, v2 varchar)
returns varchar as $$
declare
res1 varchar;
begin
 -- error
 res1 = execute format('input arguments is %I and %S', v1, v2) ;
 -- also eror
 res1 = format('input arguments is %I and %S', v1, v2);
 return res1;
end
$$ language plpgsql;

select func_format_usage(10,'Ten')

whether or not I add the execute to call the format returns, it complains error complains that there are errors around the format.

But, it is ok to run select format

I would ask where the problem is ,thanks!

CodePudding user response：

I assume that this is a syntax exercise. There are two problems:

• Use execute into instead of the assignment operator. Note that still the example wouldn't work because execute expects valid SQL. It will generate a runtime exception.
• The format placeholder %S is invalid, should be %s.

So here it is. I have replaced varchar with text which is more common in PostgreSQL.

create or replace function func_format_usage(v1 integer, v2 text)
returns text language plpgsql as
$$
declare
    res1 text;
begin
 -- execute format('input arguments is %I and %s', v1, v2) into res1;
 res1 := format('input arguments is %I and %s', v1, v2);
 return res1;
end
$$;

You can use execute like this

 execute 'select '''||format('input arguments is %I and %s',v1,v2)||'''' into res1;

which is by far an overkill and does not make much sense.