CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Shuumi
Shuumi

Reputation: 76

PostgreSQL FOUND for CREATE TABLE statements

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.

Upvotes: 1

Views: 101

Answers (2)

clemens
clemens

Reputation: 17722

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.

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45940

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.

Upvotes: 0

Related Questions