Postgresql Function execute format dynamic DDL value error

Question

So, i need to make a function that can generate schema & table that schema name and table name depend on parameter send called _schema_name for schema name & _tp_table_name for table name.

My pgsql function code right now :

CREATE OR REPLACE FUNCTION public.addschema(_schema_name character varying, _tp_table_name character varying )
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$

begin 
    
    execute format('CREATE schema %I',_schema_name);

    execute format('CREATE TABLE %I.%I( id uuid NOT NULL DEFAULT uuid_generate_v4(),
                        raw_id uuid NULL)', _schema_name, _tp_table_name);

RETURN _schema_name;
END;

$function$
;

so i run the function with this :

select addschema('newschema','new_tbl');

i've got an error like this :

SQL Error [22023]: ERROR: unrecognized format() type specifier "I"
  Hint: For a single "%" use "%%".
  Where: PL/pgSQL function addschema(character varying,character varying) line 11 at EXECUTE

anyone have any hints? thank you..

Can you execute it using psql or pgadmin? I just tested your function here and it works just fine — Jim Jones
– Jim Jones, Commented Sep 17, 2021 at 11:43

Jim Jones · Accepted Answer · 2021-09-18 09:53:25Z

1

The problem has to be somewhere in the client side. Your function looks just fine. I've tested it (slightly formatted) in psql, pgAdmin4 and DBeaver 21.2.0.202108310918.. all worked just fine:

CREATE OR REPLACE FUNCTION public.addschema(_schema_name text, _tp_table_name text )
 RETURNS text 
AS $$
BEGIN 
 EXECUTE format('CREATE SCHEMA %I;',_schema_name);
 EXECUTE format('CREATE TABLE %I.%I( id uuid NOT NULL DEFAULT uuid_generate_v4(),
                       raw_id uuid NULL)', _schema_name, _tp_table_name);
 RETURN _schema_name;
END
$$ LANGUAGE plpgsql;

DBeaver screenshot:

Alternatively, you can take look at the database log files to see how the query is actually being sent to the database. Also, in case you're using an older PostgreSQL version, format() is supported only after PostgreSQL 9.6+

edited Sep 18, 2021 at 9:53

answered Sep 17, 2021 at 12:22

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WildanVEP Over a year ago

ahh.. that's right, apparently it's only a problem in the postgres version, this happens only in local postgre but not in my project server database. thank you! solved now

Collectives™ on Stack Overflow

Postgresql Function execute format dynamic DDL value error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related