#!/bin/bash
#usage: select get_coaddh(date,hemis,scan,coadd,band) returns path and filename
#This function returns gzipped H compressed fits files
#R.Stiening May 8, 2007

psql -e -c "CREATE OR REPLACE FUNCTION get_coaddh(date,char(1),int,smallint,char(1))
            RETURNS text AS 
           'SELECT ''/image_archive/ia_''::text
            ||
            CASE
		WHEN \$1 < ''990117'' THEN ''1''
		WHEN \$1 > ''991214'' THEN ''3''
		ELSE ''2''
	    END::text
	    ||''/''
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||''/s''
            ||to_char(\$3::int,''FM000'')
            ||''/''
            ||lower(\$5)
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||to_char(\$3::int,''FM000'')
            ||''H''
            ||to_char(\$4::int,''FM000'')
            ||''.fits.H.gz''
           'LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
           " wsdb
#usage: select get_coaddh(date,hemis,scan,coadd,band,text) returns path and filename
psql -e -c "CREATE OR REPLACE FUNCTION get_coaddh(date,char(1),int,smallint,char(1),text)
            RETURNS text AS 
           'SELECT ''/image_archive/ia_''::text
            ||
            CASE
		WHEN \$1 < ''990117'' THEN ''1''
		WHEN \$1 > ''991214'' THEN ''3''
		ELSE ''2''
	    END::text
	    ||''/''
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||''/s''
            ||to_char(\$3::int,''FM000'')
            ||''/''
            ||lower(\$5)
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||to_char(\$3::int,''FM000'')
            ||''H''
            ||to_char(\$4::int,''FM000'')
            ||''.fits.H.gz''
            ||'' ''||\$6
           'LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
           " wsdb
#usage: select get_coaddh(date,hemis,scan,coadd,band,text,text) returns path and filename
psql -e -c "CREATE OR REPLACE FUNCTION get_coaddh(date,char(1),int,smallint,char(1),text,text)
            RETURNS text AS 
           'SELECT ''/image_archive/ia_''::text
            ||
            CASE
		WHEN \$1 < ''990117'' THEN ''1''
		WHEN \$1 > ''991214'' THEN ''3''
		ELSE ''2''
	    END::text
	    ||''/''
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||''/s''
            ||to_char(\$3::int,''FM000'')
            ||''/''
            ||lower(\$5)
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||to_char(\$3::int,''FM000'')
            ||''H''
            ||to_char(\$4::int,''FM000'')
            ||''.fits.H.gz''
            ||'' ''||\$6
            ||'' ''||\$7
           'LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
           " wsdb
#usage: select get_coaddh(date,hemis,scan,coadd,band,text,text,text) returns path and filename
psql -e -c "CREATE OR REPLACE FUNCTION get_coaddh(date,char(1),int,smallint,char(1),text,text,text)
            RETURNS text AS 
           'SELECT ''/image_archive/ia_''::text
            ||
            CASE
		WHEN \$1 < ''990117'' THEN ''1''
		WHEN \$1 > ''991214'' THEN ''3''
		ELSE ''2''
	    END::text
	    ||''/''
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||''/s''
            ||to_char(\$3::int,''FM000'')
            ||''/''
            ||lower(\$5)
            ||substr(\$1::text,3,2)||substr(\$1::text,6,2)||substr(\$1::text,9,2)
            ||lower(\$2)
            ||to_char(\$3::int,''FM000'')
            ||''H''
            ||to_char(\$4::int,''FM000'')
            ||''.fits.H.gz''
            ||'' ''||\$6
            ||'' ''||\$7
	    ||'' ''||\$8
           'LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
           " wsdb