Skip to main content

Posts

Showing posts from August, 2011

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,',')|