[SQL] Queyring for columns which are exist in table.
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,
c2 integer,
c3 integer
);
INSERT INTO tbl VALUES (1, 2, 3);
INSERT INTO tbl VALUES (2, 3, 4);
INSERT INTO tbl VALUES (3, 4, 5);
INSERT INTO tbl VALUES (4, 5, 6);
INSERT INTO tbl VALUES (5, 6, 7);
INSERT INTO tbl VALUES (6, 7, 8);
INSERT INTO tbl VALUES (7, 8, 9);
INSERT INTO tbl VALUES (8, 9, 10);
CREATE OR REPLACE FUNCTION ColumnAlreadyExists(name, name) RETURNS
INTEGER AS E'
DECLARE columnCount INTEGER;
BEGIN
SELECT COUNT (pg_attribute.attname) into columnCount FROM
pg_attribute,pg_class, pg_type WHERE
((pg_attribute.attrelid=pg_class.oid) AND
(pg_attribute.atttypid=pg_type.oid) AND (pg_class.relname = $1) AND
(pg_attribute.attname = $2));
IF columnCount = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION checkColumn(name,name,name);
CREATE OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS name AS
E'
DECLARE isColumnExist INTEGER;
BEGIN
SELECT ColumnAlreadyExists ($1,$2) into isColumnExist;
IF isColumnExist = 0 THEN
RETURN name($3);
ELSE
RETURN name($2);
END IF;
END;
' LANGUAGE 'plpgsql';
Function checkColumn should return proper column name (second parameter)
if column exist and third parameter if column not exist.
NOW when I try to execute following command it returns improper result.
I expect proper column values as a output of query.
SELECT(checkColumn('tbl','c2','0'))::name FROM tbl;
mydb=# SELECT (checkColumn('tbl','c2','0'))::name FROM tbl;
checkcolumn
-
c2
c2
c2
c2
c2
c2
c2
c2
(8 rows)
mydb=#
Above query should return actual values present for c2 column in tbl.
But it's not working as desired.
Please help me in this.
Thanks in advance,
Santosh.
Re: [SQL] [GENERAL] Queyring for columns which are exist in table.
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_20100125, stat1_dev2_20100126, stat1_dev2_20100127, etc. stat2_dev1_20100125, stat2_dev1_20100126, stat2_dev1_20100127, etc. stat2_dev2_20100125, stat2_dev2_20100126, stat2_dev2_20100127, etc. Now when I am upgrading my application with new version then there are some tables which are having some additional columns. In this case I have to alter each and every old tables in database with new column and it's default value. As there are large number of tables, the upgrade process is taking too much time (in days). To avoid above upgrade process I want to write a SQL statements such that it take care of newly added columns. Thanks, Santosh. -Original Message- From: Thom Brown [mailto:[email protected]] Sent: Thursday, January 27, 2011 3:09 PM To: Santosh Bhujbal (sabhujba) Cc: [email protected]; [email protected] Subject: Re: [GENERAL] Queyring for columns which are exist in table. 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 (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Compare the resulta of a count sql into bash
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}"`
> COUNTTWO=`/var/lib/pgsql/bin/./psql -U 'Thor' -dprinclocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo
> between '$FI' and '$FF'"
>
> I want to compare the result countone with countwo how does it works ?
you need -t
COUNTONE=`/var/lib/pgsql/bin/./psql -t -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`/var/lib/pgsql/bin/./psql -t -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo between
'$FI' and '$FF'"`
or like this:
CONN="user=Thor database=princlocal port=5432 host=192.170.1.82"
PSQL=/var/lib/pgsql/bin/psql
COUNTONE=`$PSQL "$CONN" -t -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`$PSQL "$CONN" -t -c "select count(*) from monterrey.$nombre where
recibo between '$FI' and '$FF'"`
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Queyring for columns which are exist in table.
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 (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Queyring for columns which are exist in table.
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_20100125, stat1_dev1_20100126, stat1_dev1_20100127, etc. > stat1_dev2_20100125, stat1_dev2_20100126, stat1_dev2_20100127, etc. > stat2_dev1_20100125, stat2_dev1_20100126, stat2_dev1_20100127, etc. > stat2_dev2_20100125, stat2_dev2_20100126, stat2_dev2_20100127, etc. > > Now when I am upgrading my application with new version then there are > some tables which are having some additional columns. > In this case I have to alter each and every old tables in database with > new column and it's default value. > As there are large number of tables, the upgrade process is taking too > much time (in days). > > To avoid above upgrade process I want to write a SQL statements such > that it take care of newly added columns. You want to refer to a column which doesn't exist, but PostgreSQL expects you to know what is available beforehand. Is there any logic to which tables have the additional column and which ones don't? For example, do all tables with the additional column have a name containing a date after a certain point in time? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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 get row number in select query
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 clause accordingly N.B. : This works as long as there is no repetition on the column you use to order. If there is, we'll need a way to tie break. What is your specific case? Also, note that this method is time consuming, and would work only for relatively small tables. AFAIK, version 8.3 doesn't have any "non-standard SQL" way to get a row number, but it is possible that something like that has been introduced in later versions... Thank you for the answer. I see psql8.4 has the method. I am not interest in ordering any columns, but just a line num. I'd like to get it from DB since displaytag _rowNum does not display row num correctly: 1, 10, 11, 12 2, 3, 4, 5, 6... Thanks, -- Lu Ying -- 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 get row number in select query
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 change the WHERE clause accordingly N.B. : This works as long as there is no repetition on the column you use to order. If there is, we'll need a way to tie break. What is your specific case? Also, note that this method is time consuming, and would work only for relatively small tables. AFAIK, version 8.3 doesn't have any "non-standard SQL" way to get a row number, but it is possible that something like that has been introduced in later versions... Thank you for the answer. I see psql8.4 has the method. I am not interest in ordering any columns, but just a line num. From your reply, I don't know if my answer did solve your problem. Did it? My idea was to supply you with a pure SQL solution, but the way it works, it does need some ordering criterion... I'd like to get it from DB since displaytag _rowNum does not display row num correctly: 1, 10, 11, 12 2, 3, 4, 5, 6... What do you mean exactly with "get it from DB" ? To Include it in an additional column on your table? Thanks, -- Lu Ying Best, Oliveiros -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- 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 get row number in select query
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 if you do want to order resultset you have to use "select from select" as numbers are added before order which may cause some performance troubles. Regards, Piotr -- -- "TECHBAZA.PL" Sp. z o.o. Technologie WEB, eDB& eCommerce Oddział Gliwice ul. Chorzowska 50 44-100 Gliwice tel. (+4832) 7186081 fax. (+4832) 7003289 -- 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 get row number in select query
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 The only disadvantage is that if you do want to order resultset you have to use "select from select" as numbers are added before order which may cause some performance troubles. You can get the row_number() without using the sub-select and without ordering the whole result as you can specify the order in the over() clause: select fileurl row_number() over (order by fileurl) from web.files Regards Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Automating PostgreSql table partition using triggers
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 written below. > > Create Table tbltesting( > testing_id int not null, > testing_name character varying(255)); > > Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting); > Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting); > > CREATE OR REPLACE FUNCTION partition_insert_trigger() > RETURNS TRIGGER AS $$ > DECLARE id integer ; > BEGIN > id := NEW.testing_id; > > INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not > sure what syntax to use here > > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > > > CREATE TRIGGER partition_trigger > BEFORE INSERT ON tbltesting > FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger(); > > Creating tables or child tables is not a problem and the trigger works fine > if the function has static definitions. What I am trying to achieve is use > the new testing_id to create a table name for use in the insert statement. > If I am able to use the variable in the table name, I would not have to > re-declare the function each time with modified conditions for each separate > testing_id. > > > With regards > > Amitabh > > Forgot to add that I am using 8.4. With regards Amitabh
[SQL] Automating PostgreSql table partition using triggers
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, testing_name character varying(255)); Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting); Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting); CREATE OR REPLACE FUNCTION partition_insert_trigger() RETURNS TRIGGER AS $$ DECLARE id integer ; BEGIN id := NEW.testing_id; INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not sure what syntax to use here RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER partition_trigger BEFORE INSERT ON tbltesting FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger(); Creating tables or child tables is not a problem and the trigger works fine if the function has static definitions. What I am trying to achieve is use the new testing_id to create a table name for use in the insert statement. If I am able to use the variable in the table name, I would not have to re-declare the function each time with modified conditions for each separate testing_id. With regards Amitabh
Re: [SQL] Automating PostgreSql table partition using triggers
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 varchar;
Field2=new. testing_name;
Field1=new.testing_id;
sql:='insert into tableName'||id||' (testing_id,testing_name) values
('||Field1||','||quote_literal(Field2)||')';
exec(sql);
On Thu, Jan 27, 2011 at 6:50 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 written below.
>
> Create Table tbltesting(
> testing_id int not null,
> testing_name character varying(255));
>
> Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
> Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
>
> CREATE OR REPLACE FUNCTION partition_insert_trigger()
> RETURNS TRIGGER AS $$
> DECLARE id integer ;
> BEGIN
> id := NEW.testing_id;
>
> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
> sure what syntax to use here
>
> RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
>
> CREATE TRIGGER partition_trigger
> BEFORE INSERT ON tbltesting
> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
>
> Creating tables or child tables is not a problem and the trigger works fine
> if the function has static definitions. What I am trying to achieve is use
> the new testing_id to create a table name for use in the insert statement.
> If I am able to use the variable in the table name, I would not have to
> re-declare the function each time with modified conditions for each separate
> testing_id.
>
>
> With regards
>
> Amitabh
>
>
--
---
Viktor Bojović
---
Wherever I go, Murphy goes with me
Re: [SQL] Automating PostgreSql table partition using triggers
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 written below. My advice is to move the table creation from a trigger to a nightly cron job. I have one that runs each night that checks for the existence of the needed tables and creates new ones for up to a month in advance and emails me the results. that way if it starts failing I've got 30 days to get things fixed. Then another script runs that creates my new triggers to work with the new partitions. Too many race conditions and performance issues with using dynamic DDL to create partitions. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Automating PostgreSql table partition using triggers
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 the query as parameter.
> it would look like this
>
> declare sql as varchar;
> Field2=new. testing_name;
> Field1=new.testing_id;
> sql:='insert into tableName'||id||' (testing_id,testing_name) values
> ('||Field1||','||quote_literal(Field2)||')';
> exec(sql);
>
> On Thu, Jan 27, 2011 at 6:50 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 written below.
>>
>> Create Table tbltesting(
>> testing_id int not null,
>> testing_name character varying(255));
>>
>> Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
>> Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
>>
>> CREATE OR REPLACE FUNCTION partition_insert_trigger()
>> RETURNS TRIGGER AS $$
>> DECLARE id integer ;
>> BEGIN
>> id := NEW.testing_id;
>>
>> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
>> sure what syntax to use here
>>
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>>
>> CREATE TRIGGER partition_trigger
>> BEFORE INSERT ON tbltesting
>> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
>>
>> Creating tables or child tables is not a problem and the trigger works
>> fine if the function has static definitions. What I am trying to achieve is
>> use the new testing_id to create a table name for use in the insert
>> statement. If I am able to use the variable in the table name, I would not
>> have to re-declare the function each time with modified conditions for each
>> separate testing_id.
>>
>>
>> With regards
>>
>> Amitabh
>>
>> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me
>
Thanks Victor. This should work out for me.
Amitabh
Re: [SQL] Automating PostgreSql table partition using triggers
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 (testing_id). A simplified structure of what I am trying to > do > > is written below. > > My advice is to move the table creation from a trigger to a nightly > cron job. I have one that runs each night that checks for the > existence of the needed tables and creates new ones for up to a month > in advance and emails me the results. that way if it starts failing > I've got 30 days to get things fixed. Then another script runs that > creates my new triggers to work with the new partitions. > > Too many race conditions and performance issues with using dynamic DDL > to create partitions. > My partitions are based on a foreign key. I would be creating child tables when a new entry is made in the parent table. With regards Amitabh
