5
I am wanting to manage a process using the resources that database
offer, as INSERT, UPDATE, DELETE.
However all this process is temporary, that is, I have to generate tables and data that as soon as the connection gets lost or is terminated should be deleted from the database.
I’m making a BEGIN; at the bank and generating all the tables and their contents, at the end ROLLBACK;
There’d be some way to make it better?
6
I think it’s all about creating temporary tables for this. Do everything on them and they can be discarded manually or automatically at the end of the/transaction section. You can create like this:
CREATE TEMP tabela ON COMMIT DROP //ou pode ser DELETE ROWS se quiser preservar a estrutura
I put in the Github for future reference.
I can’t think of a better way than not using the database.
Humm very good, I did not know, I think I will have to do ON COMMIT DROP, not to sit on the bench.
Of manual "Temporary Tables are Automatically Dropped at the end of a Session, or Optionally at the end of the Current transaction (see ON COMMIT Below)."
2
How 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(##).
Local: 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 the users that are referencing the table end their sessions.
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 little according to the Database being used. Another thing that changes is the table scope, if you’re not mistaken Mysql only offers local scope.
Before its edition did not have the Postgres tag so I put the SQL Server example. In Postgres the syntax is CREATE [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } TABLE nameTable (id integer not null, ...) You can define whether the scope will be global or local and to say that the table is named 'TEMP''
0
The best way to separate users and applications within the same base is by using schematics:
http://www.postgresql.org/docs/current/static/ddl-schemas.html
A scheme is part of the qualification of an object. When a table t is created on the basis b your 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 scheme used in the name, when not specified, is the current scheme:
b=> select current_schema;
current_schema
----------------
public
The current scheme is the first scheme in the search path that exists:
b=> show search_path;
search_path
-----------------
"$user", public
If there was a scheme with the session user name this would be the current scheme.
To make a scheme the current scheme point the search path to that scheme:
b=> set search_path to s1;
SET
b=> show search_path;
search_path
-------------
s1
If we select the current scheme it will come back null because there is no scheme S1:
b=> select current_schema;
current_schema
----------------
(1 row)
To create the scheme:
b=> create schema s1;
CREATE SCHEMA
b=> select current_schema;
current_schema
----------------
s1
Now we can create a table t in the scheme s1 without having to qualify the scheme:
b=> create table t (c text);
CREATE TABLE
As there was already a table t in the scheme public now there are two. One in the scheme public and the other in the scheme s1:
b=> \d *.t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
Table "s1.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
How the current scheme is s1 it is not necessary to qualify the name to enter 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 scheme is no longer necessary:
b=> drop schema s1 cascade;
NOTICE: drop cascades to table t
DROP SCHEMA
The cascade drops 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
Browser other questions tagged sql database postgresql
You are not signed in. Login or sign up in order to post.
You have a database/table that is normal/permanent, but you want to do operations that should be temporary? Is there a reason for this? Why not do it separately?
– Maniero
@bigown I wanted to create a separate bank for all this process, but the below did not allow, so to use the resources of a bank I would have to use the current, but should not be "traces" in it.
– Guilherme Lautert
What if you only create separate tables in the same database? What is the database?
– Maniero
@bigown edited, putting the tag, yes would be temporary tables as well as their data. The purpose of all this is to generate a files. Same as what the SPED does, only in case the SPED validates this file.
– Guilherme Lautert