#!/bin/bash
#Usage: select get_image(ra,decl,band)
#Example: select get_image(276.0,-88.0,'k')
psql -e -c "CREATE OR REPLACE FUNCTION get_image(float,float,char(1)) 
            RETURNS text AS 
           'SELECT get_coadd(date,hemis,scan,coadd,\$3,\$1::text,\$2::text)
	    FROM images 
	    WHERE distance(ra,decl,\$1,\$2)=
		(SELECT min(distance(b.ra,b.decl,\$1,\$2)) FROM images as b
		 WHERE (b.decl BETWEEN \$2 - 0.15 AND \$2 + 0.15 AND b.f_type=''M'' and b.cat=''y'')
		 AND distance(\$1,\$2,b.ra,\$2) <  256.0
	         AND distance(\$1,\$2,\$1,b.decl) < naxis2::float/2.0
                 )
            AND (decl BETWEEN \$2 -0.15 AND \$2 +0.15 and f_type=''M'' and cat=''y'')
            AND distance(\$1,\$2,ra,\$2) <  256.0
	    AND distance(\$1,\$2,\$1,decl) < naxis2::float/2.0
	    LIMIT 1
            '
            LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
            " wsdb
#Usage: select get_image(ra,decl,band,smallint)
#Example: select get_image(276.0,-88.0,'k',10)
psql -e -c "CREATE OR REPLACE FUNCTION get_image(float,float,char(1),smallint) 
            RETURNS text AS 
           'SELECT get_coadd(date,hemis,scan,coadd,\$3::text,\$1::text,\$2::text,\$4::text)
	    FROM images 
	    WHERE distance(ra,decl,\$1,\$2)=
		(SELECT min(distance(b.ra,b.decl,\$1,\$2)) FROM images as b
		 WHERE (b.decl BETWEEN \$2 - 0.15 AND \$2 + 0.15 AND b.f_type=''M'' and b.cat=''y'')
		 AND distance(\$1,\$2,b.ra,\$2) <  256.0
	         AND distance(\$1,\$2,\$1,b.decl) < naxis2::float/2.0
                 )
            AND (decl BETWEEN \$2 -0.15 AND \$2 +0.15 and f_type=''M'' and cat=''y'')
            AND distance(\$1,\$2,ra,\$2) <  256.0
	    AND distance(\$1,\$2,\$1,decl) < naxis2::float/2.0
	    LIMIT 1
            '
            LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
            " wsdb
#Usage: select get_image(ra,decl,band,integer)
#Example: select get_image(276.0,-88.0,'k',10)
psql -e -c "CREATE OR REPLACE FUNCTION get_image(float,float,char(1),integer) 
            RETURNS text AS 
           'SELECT get_coadd(date,hemis,scan,coadd,\$3::text,\$1::text,\$2::text,\$4::text)
	    FROM images 
	    WHERE distance(ra,decl,\$1,\$2)=
		(SELECT min(distance(b.ra,b.decl,\$1,\$2)) FROM images as b
		 WHERE (b.decl BETWEEN \$2 - 0.15 AND \$2 + 0.15 AND b.f_type=''M'' and b.cat=''y'')
		 AND distance(\$1,\$2,b.ra,\$2) <  256.0
	         AND distance(\$1,\$2,\$1,b.decl) < naxis2::float/2.0
                 )
            AND (decl BETWEEN \$2 -0.15 AND \$2 +0.15 and f_type=''M'' and cat=''y'')
            AND distance(\$1,\$2,ra,\$2) <  256.0
	    AND distance(\$1,\$2,\$1,decl) < naxis2::float/2.0
	    LIMIT 1
            '
            LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
            " wsdb
#Usage: select get_image(ra,decl,band,text)
#Example: select get_image(276.0,-88.0,'k','test_iomage')
psql -e -c "CREATE OR REPLACE FUNCTION get_image(float,float,char(1),text) 
            RETURNS text AS 
           'SELECT get_coadd(date,hemis,scan,coadd,\$3::text,\$1::text,\$2::text,\$4::text)
	    FROM images 
	    WHERE distance(ra,decl,\$1,\$2)=
		(SELECT min(distance(b.ra,b.decl,\$1,\$2)) FROM images as b
		 WHERE (b.decl BETWEEN \$2 - 0.15 AND \$2 + 0.15 AND b.f_type=''M'' and b.cat=''y'')
		 AND distance(\$1,\$2,b.ra,\$2) <  256.0
	         AND distance(\$1,\$2,\$1,b.decl) < naxis2::float/2.0
                 )
            AND (decl BETWEEN \$2 -0.15 AND \$2 +0.15 and f_type=''M'' and cat=''y'')
            AND distance(\$1,\$2,ra,\$2) <  256.0
	    AND distance(\$1,\$2,\$1,decl) < naxis2::float/2.0
	    LIMIT 1
            '
            LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT
            " wsdb