I'm wanting to manage a process using the resources that database
offer, as INSERT , UPDATE , DELETE .
However, this process is temporary, that is, I have to generate tables and data that, once the connection is lost or closed, should be deleted from the database.
I am making a BEGIN; in the database and generating all the tables and their contents, at the end I make ROLLBACK;
Would there be any way to improve this?
I think the point is just to create temporary tables for this. It does everything in them and they can be discarded manually or automatically at the end of the section / transaction. You can create it like this:
CREATE TEMP tabela ON COMMIT DROP //ou pode ser DELETE ROWS se quiser preservar a estrutura
I can not see a better way unless I do not use the database.
Because you generate tables and data that are temporary, use temporary tables.
CREATE TABLE #tabela_local(
id int,
descricao varchar(50))<br/>
CREATE TABLE ##tabela_global(
id int,
descricao varchar(50))
When you put the # symbol before the name, you are saying that the table is temporary. In SQL Server we have two possible scopes for a temporary table: local (#) and global (##).
Location: The table will be available to that user and will be automatically deleted when the user terminates that session.
Global: The table will be available to all logged-in users and will be deleted when users who are referencing the table end their sessions. Home
Even though the tables will be deleted, you can do a DROP TABLE when you finish your operations.
It is possible to create temporary tables with other DBMS such as MySQL and Postgres. The syntax varies a bit according to the Bank being used. Another thing that changes is the scope of the tables, if you are not mistaken MySQL only offers local scope.
The best way to separate users and applications within a single database is to use schemas:
A schema is part of the qualification of an object. When a t table is created on the base b its full name will be b.[esquema].t :
b=> create table t (c text);
CREATE TABLE
b=> \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
b=> select * from b.public.t;
c
---
(0 rows)
The schema used in the name, if not specified, is the current schema:
b=> select current_schema;
current_schema
----------------
public
The current schema is the first schema in the search path that exists:
b=> show search_path;
search_path
-----------------
"$user", public
If there was a schema with the user name of the session this would be the current schema.
To make a schema the current schema, point the search path to that schema:
b=> set search_path to s1;
SET
b=> show search_path;
search_path
-------------
s1
If we select the current scheme it will return null because there is no scheme s1:
b=> select current_schema;
current_schema
----------------
(1 row)
To create the schema:
b=> create schema s1;
CREATE SCHEMA
b=> select current_schema;
current_schema
----------------
s1
Now we can create a t table in the s1 schema without needing to qualify the schema:
b=> create table t (c text);
CREATE TABLE
Since there was already a t table in the public schema, there are now two. One in the public scheme and the other in the s1 schema:
b=> \d *.t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
Table "s1.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
As the current schema is s1 it is not necessary to qualify the name to insert in s1.t :
b=> insert into t (c) values ('texto');
INSERT 0 1
b=> select * from t;
c
-------
texto
(1 row)
b=> select * from public.t;
c
---
(0 rows)
If the schema is no longer needed:
b=> drop schema s1 cascade;
NOTICE: drop cascades to table t
DROP SCHEMA
cascade collapses all dependent objects. Go back to the default search path:
b=> set search_path to "$user", public;
SET
b=> select current_schema;
current_schema
----------------
public