Greenplum DBA useful scripts and queries

Below are two functions that can be called to build and populate a date or time dimension. Feel free to strip it for parts. You never know when it may come in handy.  

--- Call the function that builds and populates the dimension. 

select buildDateDim (date '1999-01-01', date '2013-12-31') as "Date Dim Builder"

select * from dim_date order by 1;

--- Create the builder function

CREATE OR REPLACE FUNCTION buildDateDim(startdate date, enddate date)

RETURNS text AS

$BODY$

DECLARE

strresult text default ('Table Created!');

loopDate date default (startDate);

BEGIN

execute 'drop table if exists dim_date';

execute 'create table dim_date (

date_key integer

, date_year smallint

, quarter_name char(2)

, year_quarter char(7)

, month_name varchar(15)

, month_name_short char(3)

, month_number smallint

, year_month varchar(7)

, date_value date

, day_name varchar(11)

, day_of_week_num smallint

, day_of_year smallint

, week_of_year smallint

, day_type varchar(10)

, date_formal varchar(20)

, last_30days_ind smallint

, last_60days_ind smallint

, last_90days_ind smallint

, rec_update_date date

) distributed by (date_key)';

while loopDate <= endDate loop

execute 'insert into dim_date values (' ||

to_number(to_char(loopDate, 'yyyymmdd'), 99999999) || ', ' ||

extract(year from loopDate) || ', ' ||

'''Q' || extract(quarter from loopDate) || ''', ' ||

'''' || rtrim(to_char(loopDate, 'yyyy')) || '-Q' || extract(quarter from loopDate) || ''', ' ||

'''' || rtrim(to_char(loopDate, 'Month')) || ''', ' ||

'''' || rtrim(to_char(loopDate, 'MON')) || ''', ' ||

to_number(to_char(loopDate, 'MM'), 99) || ', ' ||

'''' || rtrim(to_char(loopDate, 'yyyy-MM')) || ''', ' ||

'''' || loopDate || ''', ' ||

'''' || rtrim(to_char(loopDate, 'Day')) || ''', ' ||

extract(isodow from loopDate) || ', ' ||

to_number(to_char(loopDate, 'DDD'), 999) || ', ' ||

to_number(to_char(loopDate, 'WW'), 99) || ', ' ||

'''' || case when

rtrim(to_char(loopDate, 'Day')) in ('Saturday', 'Sunday') then 'Weekend' else 'Weekday'

end || ''', ' ||

'''' || rtrim(to_char(loopDate, 'FMMonth FMDDth, yyyy')) || ''', ' ||

case when

loopDate between (now()::date - interval '30 day') and now()::date then 1 else 0

end || ', ' ||

case when

loopDate between (now()::date - interval '60 day') and now()::date then 1 else 0

end || ', ' ||

case when

loopDate between (now()::date - interval '90 day') and now()::date then 1 else 0

end || ', ' ||

'''' || now()::date || '''' ||

')';

loopDate := loopDate + 1;

end loop;

RETURN strresult;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION builddatedim(date, date) OWNER TO gpadmin;

------

------

--- Call the function that builds and populates the dimension.

select buildTimeDim () as "Time Dim Builder"

select * from dim_time order by 1

--- Create the builder function

CREATE OR REPLACE FUNCTION buildTimeDim()

RETURNS text AS

$BODY$

DECLARE

strresult text default ('Table Created!');

loopTime time default ('0:00');

BEGIN

execute 'drop table if exists dim_time';

execute 'create table dim_time (

time_key char(4)

, hour_minute time

, time_ampm varchar(8)

, hour_range_text varchar(20)

, day_segment varchar(20)

) distributed by (time_key)';

for i in 0..1439 loop

execute 'insert into dim_time values (' ||

'''' || to_char(loopTime, 'hh24mi') || ''', ' ||

'''' || to_char(loopTime, 'hh24:mi') || ''', ' ||

'''' || to_char(loopTime, 'hh:miam') || ''', ' ||

'''' || case rtrim(to_char(loopTime, 'hh24'))

when '00' then '12am-12:59am' when '01' then '1am-1:59am' when '02' then '2am-2:59am'

when '03' then '3am-3:59am' when '04' then '4am-4:59am' when '05' then '5am-5:59am'

when '06' then '6am-6:59am' when '07' then '7am-7:59am' when '08' then '8am-8:59am'

when '09' then '9am-9:59am' when '10' then '10am-10:59am' when '11' then '11am-11:59am'

when '12' then '12pm-12:59pm' when '13' then '1pm-1:59pm' when '14' then '2pm-2:59pm'

when '15' then '3pm-3:59pm' when '16' then '4pm-4:59pm' when '17' then '5pm-5:59pm'

when '18' then '6pm-6:59pm' when '19' then '7pm-7:59pm' when '20' then '8pm-8:59pm'

when '21' then '9pm-9:59pm' when '22' then '10pm-10:59pm' when '23' then '11pm-11:59pm'

else 'other' end || ''', ' ||

'''' || case

when rtrim(to_char(loopTime, 'hh24')) between '00' and '04' then 'Late Night'

when rtrim(to_char(loopTime, 'hh24')) between '05' and '07' then 'Early Morning'

when rtrim(to_char(loopTime, 'hh24')) between '08' and '11' then 'Morning'

when rtrim(to_char(loopTime, 'hh24')) between '12' and '16' then 'Afternoon'

when rtrim(to_char(loopTime, 'hh24')) between '17' and '20' then 'Evening'

else 'Night' end || '''' ||

')';

loopTime := loopTime + interval '1 minute';

end loop;

RETURN strresult;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION builddatedim(date, date) OWNER TO gpadmin;