Re: [SQL] Automating PostgreSql table partition using triggers

2011-01-27 Thread Amitabh Kant
On Fri, Jan 28, 2011 at 2:57 AM, Scott Marlowe wrote: > On Thu, Jan 27, 2011 at 10:50 AM, Amitabh Kant > wrote: > > Hi > > > > I am trying to write a function which is being called from a trigger used > > for partitioning a large table. The partitioning is to happen based on an > > integer field

Re: [SQL] Automating PostgreSql table partition using triggers

2011-01-27 Thread Amitabh Kant
On Fri, Jan 28, 2011 at 2:40 AM, Viktor Bojović wrote: > when creating dynamic query try to store it completey as string because you > will not be able to change tableName (i havent been doing that for a long > time , so possibly i can be wrong). > to "exec" or "execute" command you have to pass t

Re: [SQL] Automating PostgreSql table partition using triggers

2011-01-27 Thread Scott Marlowe
On Thu, Jan 27, 2011 at 10:50 AM, Amitabh Kant wrote: > Hi > > I am trying to write a function which is being called from a trigger used > for partitioning a large table. The partitioning is to happen based on an > integer field (testing_id). A simplified structure of what I am trying to do > is w

Re: [SQL] Automating PostgreSql table partition using triggers

2011-01-27 Thread Viktor Bojović
when creating dynamic query try to store it completey as string because you will not be able to change tableName (i havent been doing that for a long time , so possibly i can be wrong). to "exec" or "execute" command you have to pass the query as parameter. it would look like this declare sql as v

[SQL] Automating PostgreSql table partition using triggers

2011-01-27 Thread Amitabh Kant
Hi I am trying to write a function which is being called from a trigger used for partitioning a large table. The partitioning is to happen based on an integer field (testing_id). A simplified structure of what I am trying to do is written below. Create Table tbltesting( testing_id int not null,

Re: [SQL] Automating PostgreSql table partition using triggers

2011-01-27 Thread Amitabh Kant
On Thu, Jan 27, 2011 at 11:20 PM, Amitabh Kant wrote: > Hi > > I am trying to write a function which is being called from a trigger used > for partitioning a large table. The partitioning is to happen based on an > integer field (testing_id). A simplified structure of what I am trying to do > is w

Re: [SQL] how to get row number in select query

2011-01-27 Thread Thomas Kellerer
Piotr Czekalski, 27.01.2011 16:21: Gentelmen, I follow this thread and I don't exactly get an idea of yours, but isn't is as simple as (example: table "web.files" contains one column named "fileurl" ): select row_number() over(), X.fileurl from (select fileurl from web.files order by fileurl) X

Re: [SQL] how to get row number in select query

2011-01-27 Thread Piotr Czekalski
Gentelmen, I follow this thread and I don't exactly get an idea of yours, but isn't is as simple as (example: table "web.files" contains one column named "fileurl" ): select row_number() over(), X.fileurl from (select fileurl from web.files order by fileurl) X The only disadvantage is that

Re: [SQL] how to get row number in select query

2011-01-27 Thread Oliveiros d'Azevedo Cristina
Hi Oliveiros, Howdy! If it is to order in ascendent fashion by, say, lname, one possibility would be SELECT COUNT(b.*) as row_number, a.lname,a.gname FROM "Table1" a, "Table2" b WHERE a.lname >= b.lname GROUP BY a.lname,a.gname ORDER BY row_number If you want to order by gname just chang

Re: [SQL] how to get row number in select query

2011-01-27 Thread Emi Lu
Hi Oliveiros, If it is to order in ascendent fashion by, say, lname, one possibility would be SELECT COUNT(b.*) as row_number, a.lname,a.gname FROM "Table1" a, "Table2" b WHERE a.lname >= b.lname GROUP BY a.lname,a.gname ORDER BY row_number If you want to order by gname just change the WHERE c

Re: [SQL] [GENERAL] Queyring for columns which are exist in table.

2011-01-27 Thread Thom Brown
On 27 January 2011 09:53, Santosh Bhujbal (sabhujba) wrote: > Hi Thom, > > Thank you for your response. > > I have a application which is periodically gathering diff stats from > diff devices and put them into database. > Tables are created per stat, per device and per day. > e.g. stat1_dev1_20100

Re: [SQL] [GENERAL] Queyring for columns which are exist in table.

2011-01-27 Thread Thom Brown
On 27 January 2011 07:52, Santosh Bhujbal (sabhujba) wrote: > Hi All, > > > > I want to fire a query such that if the particular column does not exist > then query should return some default value. Why do you want to do this? What is it you using this for? -- Thom Brown Twitter: @darkixion IRC

Re: [SQL] Compare the resulta of a count sql into bash

2011-01-27 Thread Jasen Betts
On 2011-01-26, manuel antonio ochoa wrote: > --0015174be152ceb275049ac2dc95 > Content-Type: text/plain; charset=ISO-8859-1 > > Hello, > I have the next : > > COUNTONE=`/var/lib/pgsql/bin/./psql -U 'Thor' -d princlocal -p 5432 -h > 192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"` >

Re: [SQL] [GENERAL] Queyring for columns which are exist in table.

2011-01-27 Thread Santosh Bhujbal (sabhujba)
Hi Thom, Thank you for your response. I have a application which is periodically gathering diff stats from diff devices and put them into database. Tables are created per stat, per device and per day. e.g. stat1_dev1_20100125, stat1_dev1_20100126, stat1_dev1_20100127, etc. stat1_dev2_2010012

[SQL] Queyring for columns which are exist in table.

2011-01-27 Thread Santosh Bhujbal (sabhujba)
Hi All, I want to fire a query such that if the particular column does not exist then query should return some default value. For that I have tried following experiment. SETUP details: Platform : Sun Solaris 5.10 Postgres : 8.3.7 CREATE TABLE tbl ( c1 integer,