[SQL] How To Create Temporary Table inside a function
hi everyone ,
how can i create temp table say table1 with three column of types varchar , int
and int, inside the function body and if that same table already exist it
should drop that already existing table(table1) , and on every run this process
should run. and how can i insert values from postgresql view say vwfirst to
this table(table1)
Here is the MS-SQL code for this process but i want to know how can this be
done in postgresql
if object_id('tempdb..#tblTFSites1') is not null
drop table tempdb..#tblTFSites1
create table #tblTFSites1(
Chr varchar(50) NULL,
Start int NULL,
[End] int NULL
)
INSERTION PROCESS
INSERT INTO #tblTFSites1 (Chr, Start,[End])
Select Chr_U, Start_U, End_U from vwChrCompareSites where KBId=@ID and
UserDataDetailId=@UserDataDetailId
and bpOverlap >= @bpOverlap and (CentreDistance <= @CentreDistance or
@CentreDistance=1)
Re: [SQL] How To Create Temporary Table inside a function
Hi Rehan
I suggest attempting to drop the table before you create the temp table:
DROP TABLE IF EXISTS table1;
See:
http://www.postgresql.org/docs/current/static/sql-droptable.html
Also if you're using an actual TEMP table, PostgreSQL can automatically
drop the table or just empty it once the transaction is committed:
CREATE TEMP TABLE foo (columns...) ON COMMIT DROP;
CREATE TEMP TABLE foo (columns...) ON COMMIT DELETE ROWS;
See:
http://www.postgresql.org/docs/current/static/sql-createtable.html
As for filling the table, you have two options, INSERT ... SELECT ...
Which is just the same syntax as you've used previously for MS SQL or
create the table with data already in it it:
CREATE TEMP TABLE foo ON COMMIT DROP AS SELECT ...
See:
http://www.postgresql.org/docs/current/static/sql-createtableas.html
Hope this helps.
On 02/03/2012 11:23, Rehan Saleem wrote:
> hi everyone ,
> how can i create temp table say table1 with three column of types
> varchar , int and int, inside the function body and if that same table
> already exist it should drop that already existing table(table1) , and
> on every run this process should run. and how can i insert values from
> postgresql view say vwfirst to this table(table1)
>
> Here is the MS-SQL code for this process but i want to know how can
> this be done in postgresql
>
> if object_id('tempdb..#tblTFSites1') is not null
> drop table tempdb..#tblTFSites1
>
> create table #tblTFSites1(
> Chr varchar(50) NULL,
> Start int NULL,
> [End] int NULL
> )
>
> INSERTION PROCESS
>
> INSERT INTO #tblTFSites1 (Chr, Start,[End])
> Select Chr_U, Start_U, End_U from vwChrCompareSites where KBId=@ID and
> UserDataDetailId=@UserDataDetailId
> and bpOverlap >= @bpOverlap and (CentreDistance <= @CentreDistance or
> @CentreDistance=1)
>
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to shrink database in postgresql
2012/2/29 Pavel Stehule : > Hello > > the most similar tool in pg is "VACUUM FULL" statemet; Hello, From: http://wiki.postgresql.org/wiki/VACUUM_FULL "Many people, either based on misguided advice on the 'net or on the assumption that it must be "better", periodically run VACUUM FULL on their tables. This is generally a really bad idea and can make your database slower not faster", it is better to use autovacuum. Please read the wiki, you'll find all the information you need. Regards, Mario Marín -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
iamunix=# \c postgres was really meant to be: iamunix=# \c - postgres The first changes to database postgres as current user, the second changes the user while remaining on the current database. This is very helpful! psql> \c - username_for_new_connection -- Emi -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How To Create Temporary Table inside a function
On Fri, Mar 2, 2012 at 3:49 AM, Philip Couling wrote: > Hi Rehan > > I suggest attempting to drop the table before you create the temp table: > DROP TABLE IF EXISTS table1; > > See: > http://www.postgresql.org/docs/current/static/sql-droptable.html > > > Also if you're using an actual TEMP table, PostgreSQL can automatically > drop the table or just empty it once the transaction is committed: > CREATE TEMP TABLE foo (columns...) ON COMMIT DROP; > CREATE TEMP TABLE foo (columns...) ON COMMIT DELETE ROWS; > > See: > http://www.postgresql.org/docs/current/static/sql-createtable.html > > > As for filling the table, you have two options, INSERT ... SELECT ... > Which is just the same syntax as you've used previously for MS SQL or > create the table with data already in it it: > CREATE TEMP TABLE foo ON COMMIT DROP AS SELECT ... > > See: > http://www.postgresql.org/docs/current/static/sql-createtableas.html > > Hope this helps. > Rehan, 100% of the information in this email is available in the postgresql documentation in locations where it would be trivially easy to find if any attempt at all was made to look in the documentation prior to coming to the mailing list for assistance. It is fruitless to attempt to work as a database administrator or developer if you do not first familiarize yourself with its documentation. The mailing lists are here to assist users with difficult-to-solve problems, not as substitute for reading the manual. Answering easily solved questions takes limited resources away from users who may have more intractable issues that they need assistance with. I can't help but notice that this isn't even the first question you've posed to which responses have included suggestions that you read the documentation. Please consider taking our advice. --sam
