Postgresql – procedure for postgres to create table if not exist

informaticapostgresql

i want to create table if that doesn't, i tried below code:

create or replace function create_table() returns void as
$$
begin
    if not exists(select * 
                  from   pg_tables 
                  where  schemaname = 'Public'
                  and    tablename = 'test') 
    then

        create table test
        (
            the_id int not null,
            name text
        );

    end if;

end;
$$
language 'plpgsql';

While executing this procedure first time:

select creat_table();

table gets created but when I execute it again I get the below error:

ERROR:  relation "test" already exists
CONTEXT:  SQL statement "create table test
        (
            the_id int not null,
            name text
        )"
PL/pgSQL function create_table() line 8 at SQL statement
********** Error **********

ERROR: relation "test" already exists
SQL state: 42P07
Context: SQL statement "create table test
        (
            the_id int not null,
            name text
        )"
PL/pgSQL function create_table() line 8 at SQL statement

How to achieve this, and also I want to call this procedure from Informatica pre-sql target session property so i want to call procedure with table name as parameter.

Best Answer

Just use CREATE TABLE [IF NOT EXISTS]

Looks like this,

CREATE TABLE IF NOT EXISTS test (
  the_id  int   PRIMARY KEY,
  name    text
);

If you must wrap it in a function (don't though, there is no point),

CREATE FUNCTION myCreateTable() RETURNS void AS $$
  CREATE TABLE IF NOT EXISTS test (
    the_id  int   PRIMARY KEY,
    name    text
  );
$$ LANGUAGE sql
VOLATILE;

If you want that function to accept a name (still wouldn't suggest this),

CREATE OR REPLACE FUNCTION myCreateTable(myIdent text) RETURNS void AS $$
  BEGIN
    EXECUTE format(
      '
        CREATE TABLE IF NOT EXISTS %I (
          the_id  int   PRIMARY KEY,
          name    text
        );
      ',
      myIdent
    );
  END;
$$ LANGUAGE plpgsql
VOLATILE;

[ IF NOT EXISTS ] has been in PostgreSQL since 9.1

Related Solutions

Simultaneous Calls to the Same Function – How Deadlocks Occur

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table:

I believe the issue with this is that a SQL function will do parsing (and maybe planning too; don't feel like checking the code right now) for the entire function body at once. This means that due to the INSERT command you acquire RowExclusiveLock on the "test" table during function body parsing, before the LOCK command actually executes. So the LOCK represents a lock escalation attempt, and deadlocks are to be expected.

This coding technique would be safe in plpgsql, but not in a SQL-language function.

There have been discussions of reimplementing SQL-language functions so that parsing occurs one statement at a time, but don't hold your breath about something happening in that direction; it doesn't seem to be a high priority concern for anybody.

regards, tom lane

This also explains why locking the table outside the function in a wrapping plpgsql block (as suggested by @ypercube) prevents the deadlocks.

PostgreSQL – Partition Trigger and Checking for Child Table

I would advise to use always quote_ident when generating dynamic sentences, to make sure your idents don't have upper/lowercase problems, or that you can use identifiers with spaces or special chars, and that you avoid SQL injection if your idents are parameters out of your control.

That is, your trigger function should look like:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
  start_ts TIMESTAMP ;
  end_ts TIMESTAMP ;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;
  start_ts := partition_date::timestamp ;
  end_ts := start_ts + interval '1 day' ;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE '    || quote_ident(partition) 
        || ' (check (updatetimestamp >= ''' || start_ts || ''' ' 
        || '     AND updatetimestamp <  ''' || end_ts || ''')) ' 
        || ' INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %', partition;
  END IF;
  EXECUTE 'INSERT INTO ' || quote_ident(partition) || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Check a usage example for quote_literal on the PostgreSQL documentation.

As a matter of preference, I prefer to have the names of all tables lowercase and with no special chars (so that I don't have "Table Names with Double Quotes", but table_names_without_quotes), because I think the code is less noisy and more readable. But, again, this is a matter of taste.

Best Answer

Related Solutions

Simultaneous Calls to the Same Function – How Deadlocks Occur

PostgreSQL – Partition Trigger and Checking for Child Table

Related Question