I am creating a function that will create a new table and insert informations about that table into other tables.
To create that table I am using the
CREATE TABLE IF NOT EXISTS
statement. Sadly it does not update the FOUND special variable in PostgreSQL nor can i find any other variable that would be updated.
Is there any way in PL/PGSQL to know whether that statement created a table or not?
The target of it is to not to double the informations in the other tables.
You may use CREATE TABLE AS in combination with ON_ERROR_ROLLBACK:
BEGIN;
-- Do inital stuff
\set ON_ERROR_ROLLBACK on
CREATE TABLE my_table AS
SELECT id, name FROM (VALUES (1, 'Bob'), (2, 'Mary')) v(id, name);
\set ON_ERROR_ROLLBACK off
-- Do remaining stuff
END;
To put it bluntly, with \set ON_ERROR_ROLLBACK on postgres will create a savepoint before each statement and automatically rollback to this savepoint or releasing it depending on the success of that statement.
The code above will execute initial and remaining stuff even if the table creation fails.
No, there are not any information if this command created table or not. The found variable is updated after query execution - not after DDL command. There is guaranteed so after this command, the table will be or this command fails to an exception.
CREATE TABLEnot insert anything if the rows are already there (e.g. usingon conflict do nothing)