Skip to main content

Posts

PostgreSQL Database Growth

Hi , Here you can find the code for finding the DB Growth Percentage in PostgreSQL. Required TABLE:- ------------------- CREATE TABLE PGBAR_PGG.DBSIZES(TIME_STMP TIMESTAMP DEFAULT NOW(),DATNAME VARCHAR(100) NOT NULL,DATSIZE BIGINT NOT NULL,DBGROWTH VARCHAR(100),DBGROWTHPERCENT REAL); Required Function For Trigger:- --------------------------------- CREATE OR REPLACE FUNCTION PGBAR_PGG.FEED_DB_GROWTH() RETURNS TRIGGER LANGUAGE PLPGSQL AS $FUNCTION$ DECLARE CHECK_DB_PRESENCE INT; PREV_DB_SIZE BIGINT; BEGIN SELECT COUNT(*) INTO CHECK_DB_PRESENCE FROM PGBAR_PGG.DBSIZES WHERE LOWER(DATNAME)=NEW.DATNAME; IF(CHECK_DB_PRESENCE=0) THEN RETURN NEW; ELSE SELECT DATSIZE INTO PREV_DB_SIZE FROM PGBAR_PGG.DBSIZES WHERE (CTID,DATNAME) IN (SELECT MAX(CTID),DATNAME FROM PGBAR_PGG.DBSIZES GROUP BY DATNAME HAVING DATNAME=NEW.DATNAME); IF(PREV_DB_SIZE!=NEW.DATSIZE) THEN SELECT PG_SIZE_PRETTY(NEW.DATSIZE::BIGINT-PREV_DB_SIZE) INTO NEW.DBGROWTH; SELECT ((NEW.DATSIZE::REAL-PREV_DB_...

Circle in C with ASCII

Hi , Here is the small program,generating a circle in c with ASCII in linux environment. Surface:50X50 Radius:15 Formula: (x-CC)2+(y-CC)2=radius2 Here CC=Circle Center x,y are the surface pointers. #include "stdio.h" #include "math.h" int main() { int i,j,rad; rad=15; for (i=0; i { for (j=0; j { if(sqrt((j-(50)/2)*(j-(50)/2) + (i-(50)/2)*(i-(50)/2)) printf("."); else printf(" "); } printf("\n"); } return 0; } Complile:- -------------- gcc -o circ circ.c -lm

Table related sequences

Hi, Here is the query in PostgreSQL finds the list of tables and it's related sequences. postgres=# SELECT TAB_SEQ_LIST.TABLE,TAB_SEQ_LIST.attname AS Columnname,TAB_SEQ_LIST.nspname AS Schemaname,case when position((nspname||'.')::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)=0 and length(TAB_SEQ_LIST.sequence_def)>=1 then 'public.'||sequence_def else (select nspname from pg_class,pg_namespace where relkind='S' and relnamespace=pg_namespace.oid and relname=TAB_SEQ_LIST.sequence_def and pg_namespace.nspname=TAB_SEQ_LIST.nspname)::text||'.'||TAB_SEQ_LIST.sequence_def end as sequence_name FROM ( SELECT a.attrelid::regclass as table,attrelid ,attname,nspname,(select trim(both '''' from rtrim(ltrim(regexp_replace(regexp_replace(regexp_repla...

Calendar Query in PostgreSQL

Here you can find a query 2011 year calendar and list of holidays(only one per a week(Maharastar Holidays) ) which has been written in PostgreSQL. QUERY ======== select min(case when extract(month from d.dd)= 1 then 'Jan' when extract(month from d.dd)= 2 then 'Feb' when extract(month from d.dd)=3 then 'Mar' when extract(month from d.dd)= 4 then 'Apr' when extract(month from d.dd)= 5 then 'May' when extract(month from d.dd)= 6 then 'Jun' when extract(month from d.dd)= 7 then 'Jul' when extract(month from d.dd)= 8 then 'Aug' when extract(month from d.dd)=9 then 'Sep' when extract(month from d.dd)= 10 then 'Oct' when extract(month from d.dd)= 11 then 'Nov' when extract(month from d.dd)= 12 then 'Dec' end ) as MONTH,min(case when extract(DOW from d.dd)=0 then (extract(day from d.dd)) end) as SUN, min(case when extract(DOW from d.dd)=1 then (extract(day from d....

Excepting One or More Fields Of A Table In Postgres

If you have n no.of columns in a table, and if you need to select only m no.of columns from that then below is the procedure for you. CREATE OR REPLACE FUNCTION XCEPT(TABNAME TEXT,VARIADIC COLNAMES TEXT[]) RETURNS VOID AS $$ DECLARE CHEK INT:=0; CHEK1 INT:=0; NO_OF_COLS INT:=0; ALLCOLS TEXT:=' '; LC INT:=1; I RECORD; J INT; PR RECORD; QUERY TEXT:=' '; NX TEXT; BEGIN SELECT 1::INT INTO CHEK FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME); SELECT COUNT(*) INTO NO_OF_COLS FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME); IF (CHEK=1) THEN FOR I IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=LOWER(TABNAME)) LOOP FOR J IN 1..NO_OF_COLS LOOP IF (LOWER(COLNAMES[J])::TEXT=LOWER(I.COLUMN_NAME)) THEN CHEK1:=1; END IF; END LOOP; IF(CHEK1=0) THEN ALLCOLS:=ALLCOLS||I.COLUMN_NAME||','; END IF; CHEK1:=0; END LOOP; RAISE NOTICE '%','SELECT '||TRIM(ALLCOLS,',')|...

ROLLUP IN ORACLE

ROLLUP ======= Extension of Group By Clause, which Provide super aggregate rows. Ex:- ----- Query for sum of salaries by department and also display the total salary of all the departments. Using Set Operator ------------------- SQL> SELECT DEPTNO,SUM(SAL) 2 FROM 3 EMP GROUP BY DEPTNO 4 UNION 5 SELECT NULL,SUM(SAL) 6 FROM 7 EMP; DEPTNO SUM(SAL) ---------- ---------- 10 7450 20 10875 30 9400 50 1300 29025 Using Inline Views -------------------- SQL> SELECT DEPTNO,SUM(SAL) DEPTSAL,A.TOTALSAL TOTALSALARY 2 FROM 3 EMP,(SELECT SUM(SAL) TOTALSAL 4 FROM 5 EMP) A 6 GROUP BY DEPTNO,A.TOTALSAL; DEPTNO DEPTSAL TOTALSALARY ---------- ---------- ----------- 10 7450 29025 20 10875 30 9400 50 1300 Using Rollup ------------- SQL> SELECT DEPTNO,SUM(SAL) 2 FROM 3 EMP 4 GROUP BY ROLLUP(DEPTNO); DEPTNO SUM(SAL) ---------- ---------- 10 7450 20 10875 30 ...