From: Philip Couling <phil@pedal.me.uk>
To: Rehan Saleem <pk_rehan@yahoo.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: How To Create Temporary Table inside a function
Date: Fri, 02 Mar 2012 11:49:31 +0000
Message-ID: <4F50B3CB.9030604@pedal.me.uk> (raw)
In-Reply-To: <1330687383.77274.YahooMailNeo@web121602.mail.ne1.yahoo.com>
References: <1330687383.77274.YahooMailNeo@web121602.mail.ne1.yahoo.com>
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)
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: phil@pedal.me.uk, pk_rehan@yahoo.com
Subject: Re: How To Create Temporary Table inside a function
In-Reply-To: <4F50B3CB.9030604@pedal.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox