Create function to create table Postgresql

Question

I am trying to create table inside POSTGRESQL function but getting following error.

ERROR: no schema has been selected to create in CONTEXT: SQL statement " CREATE TABLE IF NOT EXISTS test.t_testing ( id serial PRIMARY KEY, customerid int, daterecorded date, value double precision )"

My function is as follows.

CREATE OR REPLACE FUNCTION test.create_table_type1(t_name varchar(30))
  RETURNS VOID AS
$func$
BEGIN

EXECUTE format('
   CREATE TABLE IF NOT EXISTS %I (
    id serial PRIMARY KEY,
    customerid int,
    daterecorded date,
    value double precision
   )', 'test.t_' || t_name);

END
$func$ LANGUAGE plpgsql;

Kristo Mägi · Accepted Answer · 2016-11-05 10:33:07Z

5

Try this instead:

CREATE OR REPLACE FUNCTION test.create_table_type1(t_name varchar(30))
  RETURNS VOID AS
$func$
BEGIN

EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I.%I (
        id serial       PRIMARY KEY,
        customerid      int,
        daterecorded    date,
        value           double precision
    )',
   'test',
   ( 't_' || t_name )
);

END
$func$ LANGUAGE plpgsql;

So sepparate 'schema' and 'table'.

answered Nov 5, 2016 at 10:33

Kristo Mägi

1,70413 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Create function to create table Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related