-- Creates functions for HyperLeda
--   1) function hl_names usable in fullsql to return the whole
--      list of designation
--      usage: hl_names(pgc)
--
--
--   2) celpos functions used to print celestial positions in sexagesimal
--      string
--      Usage: 
--        celposB(pgc)
--        celposJ(pgc)
--        celpos_val(symbol,ra,dec)
--     
--      Description
--      Two of them use pgc id as the only input parameter, they make selection
--      themselves. They are very bright for the end user, but quite slow. The
--      third one doesn't make any selections, it only uses ra and dec provided
--      as the arguments and adds symbol to the beginning. It looks like 
--      "celpos_val" is at least 2 times faster (this is clear) when query is 
--      cached and tens or hundreds times faster when it is not (it depends 
--      on the amount of selected records).
--      Note: (Ph. Prugniel 2006/04/27) 
--          a) there is a rounding bug in _celpos_val
--          b) I do not know if this function is used anywhere, probably
--             should be removed.
--      
--      Examples:
--  	  hl=# select celposB(2557);
--  	       celposb
--  	  ------------------
--  	   B004000.1+405944
--  	  (1 row)
--  	  
--  	  hl=# select celposJ(2557);
--  	       celposj
--  	  ------------------
--  	   J004244.3+411610
--  	  (1 row)
--  	  
--  	  hl=# select celpos_val('J',11.22,-0.223);
--  	      celpos_val
--  	  ------------------
--  	   J111312.0-001323
--  	  (1 row)
--
--
--   3) hl_group returns list of PGC-numbers as a table.
--      Usage: hl_group(name)
--
--      Description:
--      hl_group finds parameters of group in m000_group table for given object <name>.
--      Using coordinates of the center, mean velocity, radius and velocity dispersion of the group 
--      it produces a cone search for objects belongin to the group.
--
--      Example:
--        hl=# SELECT count(*) FROM hl_group('m87');
--         count 
--        -------
--          271
--        (1 row)

------------------------------------------------------------------------------
-- 2017/01/19 D. Makarov
-- add hl_group function
-- add IMMUTABLE to coma_str, coma_string, hl_names, celpos_val, celposb, celposj declarations.

-------------------------------------------------------------------------------
-- Modified by Dmitry Makarov 2014/10/24
-- replace quite (') in function declaration with double-dollar ($$)
-- replace escape string constant (\') with single quote (')

-------------------------------------------------------------------------------
-- Written by Igor Chilingarian, 2003/01/14
-- hl_names returns the list of designations associated with a pgc internal
-- number

CREATE OR REPLACE FUNCTION coma_str(varchar,varchar) RETURNS varchar
AS $$
    DECLARE
        retval varchar:='';
    BEGIN
        IF ($2 = '') OR (($1 = '') AND ($2 <> '')) THEN
            retval := $2;
        ELSE
            retval := $1 || ',' || $2;
        END IF;
        RETURN retval;
    END
$$ LANGUAGE 'plpgsql' IMMUTABLE;

CREATE  AGGREGATE coma_string ( BASETYPE = varchar,
        SFUNC = coma_str, STYPE = varchar, INITCOND='');

CREATE OR REPLACE FUNCTION hl_names(int4) RETURNS varchar AS $$
SELECT coma_string(design)
                FROM designation WHERE PGC = $1;
$$ LANGUAGE SQL IMMUTABLE;

-- hl_names(varchar), Ph. Prugniel 2007/10/30
CREATE OR REPLACE FUNCTION hl_names(varchar) RETURNS varchar AS $$
    DECLARE
        pgcr RECORD;
        ret_val RECORD;
    BEGIN
        SELECT INTO pgcr pgc FROM m000 WHERE objname=objname($1);
        IF NOT FOUND THEN
	    RETURN NULL;
        END IF;
	SELECT INTO ret_val coma_string(design)
                FROM designation WHERE pgc = pgcr.pgc;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'No name for pgc=(%)', pgcr.pgc;
        END IF;

	RETURN ret_val.coma_string;
    END
$$ LANGUAGE 'plpgsql' IMMUTABLE;


-------------------------------------------------------------------------------
-- Written by Igor Chilingarian 2003/01/27

-- Debugged by Ph. Prugniel 2006/04/27

-- The functions celposB and celposJ must be recoded in C using the
-- standard Pleinpot functions in order to simplify maintenance

-- These functions are obsolete. Use calpos from pleinpot_sql instead.

CREATE OR REPLACE FUNCTION celposB(int4) RETURNS varchar AS $$
    DECLARE
        b1950 RECORD;
        ra_h   int;
        ra_m   int;
        ra_10s   int;
        ra_hs   varchar;
        ra_ms   varchar;
        ra_ss   varchar;

        absdec  float8;
        dec_d   int;
        dec_m   int;
        dec_s   int;
        dec_sign varchar;
        dec_ds   varchar;
        dec_ms   varchar;
        dec_ss   varchar;

        retval varchar:='';
    BEGIN
        SELECT INTO b1950 al1950,de1950 FROM meandata WHERE pgc = $1;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'object with PGC% not found', $1;
        END IF;

        ra_h := floor(b1950.al1950);
        ra_m := floor(60.0*(b1950.al1950 - ra_h));
        ra_10s := round(36000*(b1950.al1950-ra_h-ra_m/60.0));
        IF (ra_10s = 600) THEN
            ra_10s := 0;
            ra_m := ra_m + 1; 
        END IF;
        IF (ra_m = 60) THEN
            ra_m := 0;
            ra_h := ra_h + 1; 
        END IF;

        IF (ra_h < 10) THEN
            ra_hs := '0'||ra_h;
        ELSE
            ra_hs := ra_h;
        END IF;
        IF (ra_m < 10) THEN
            ra_ms := '0'||ra_m;
        ELSE
            ra_ms := ra_m;
        END IF;
        IF (ra_10s < 100) THEN
            ra_ss := '0'||floor(ra_10s/10)||'.'|| (ra_10s % 10);
        ELSE
            ra_ss := floor(ra_10s/10)||'.'|| (ra_10s % 10);
        END IF;


        IF ( (b1950.de1950) < 0) THEN
            dec_sign := '-';
        ELSE
            dec_sign := '+';
        END IF;

        absdec := abs(b1950.de1950);
        dec_d := floor(absdec);
        dec_m := floor(60.0*(absdec - dec_d));
        dec_s := round(3600*(absdec-dec_d-dec_m/60.0));
        IF (dec_s = 60) THEN
            dec_m := dec_m + 1;
            dec_s := 0;
        END IF;
        IF (dec_m = 60) THEN
            dec_d := dec_d + 1;
            dec_m := 0;
        END IF;

        IF (dec_d < 10) THEN
            dec_ds := '0'||dec_d;
        ELSE
            dec_ds := dec_d;
        END IF;
        IF (dec_m < 10) THEN
            dec_ms := '0'||dec_m;
        ELSE
            dec_ms := dec_m;
        END IF;
        IF (dec_s < 10) THEN
            dec_ss := '0'||dec_s;
        ELSE
            dec_ss := dec_s;
        END IF;

        retval := 'B' || ra_hs || ra_ms || ra_ss || dec_sign || 
                  dec_ds || dec_ms || dec_ss ;
        RETURN retval;
    END
$$ LANGUAGE 'plpgsql' IMMUTABLE;

CREATE OR REPLACE FUNCTION celposJ(int4) RETURNS varchar 
AS $$
    DECLARE
        j2000 RECORD;
        ra_h   int;
        ra_m   int;
        ra_10s   int;
        ra_hs   varchar;
        ra_ms   varchar;
        ra_ss   varchar;

        absdec  float8;
        dec_d   int;
        dec_m   int;
        dec_s   int;
        dec_sign varchar;
        dec_ds   varchar;
        dec_ms   varchar;
        dec_ss   varchar;

        retval varchar:='';
    BEGIN
        SELECT INTO j2000 al2000,de2000 FROM meandata WHERE pgc = $1;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'object with PGC% not found', $1;
        END IF;

        ra_h := floor(j2000.al2000);
        ra_m := floor(60.0*(j2000.al2000 - ra_h));
        ra_10s := round(36000*(j2000.al2000-ra_h-ra_m/60.0));
        IF (ra_10s = 600) THEN
            ra_10s := 0;
            ra_m := ra_m + 1; 
        END IF;
        IF (ra_m = 60) THEN
            ra_m := 0;
            ra_h := ra_h + 1; 
        END IF;

        IF (ra_h < 10) THEN
            ra_hs := '0'||ra_h;
        ELSE
            ra_hs := ra_h;
        END IF;
        IF (ra_m < 10) THEN
            ra_ms := '0'||ra_m;
        ELSE
            ra_ms := ra_m;
        END IF;
        IF (ra_10s < 100) THEN
            ra_ss := '0'||floor(ra_10s/10)||'.'|| (ra_10s % 10);
        ELSE
            ra_ss := floor(ra_10s/10)||'.'|| (ra_10s % 10);
        END IF;


        IF ( (j2000.de2000) < 0) THEN
            dec_sign := '-';
        ELSE
            dec_sign := '+';
        END IF;

        absdec := abs(j2000.de2000);
        dec_d := floor(absdec);
        dec_m := floor(60.0*(absdec - dec_d));
        dec_s := round(3600*(absdec-dec_d-dec_m/60.0));
        IF (dec_s = 60) THEN
            dec_m := dec_m + 1;
            dec_s := 0;
        END IF;
        IF (dec_m = 60) THEN
            dec_d := dec_d + 1;
            dec_m := 0;
        END IF;

        IF (dec_d < 10) THEN
            dec_ds := '0'||dec_d;
        ELSE
            dec_ds := dec_d;
        END IF;
        IF (dec_m < 10) THEN
            dec_ms := '0'||dec_m;
        ELSE
            dec_ms := dec_m;
        END IF;
        IF (dec_s < 10) THEN
            dec_ss := '0'||dec_s;
        ELSE
            dec_ss := dec_s;
        END IF;

        retval := 'J' || ra_hs || ra_ms || ra_ss || dec_sign || 
                  dec_ds || dec_ms || dec_ss ;
        RETURN retval;
    END
$$ LANGUAGE 'plpgsql' IMMUTABLE;

CREATE OR REPLACE FUNCTION celpos_val(char,float8, float8) RETURNS varchar 
AS $$
    DECLARE
        ra_val  ALIAS FOR $2;
        dec_val ALIAS FOR $3;
        eq_val  ALIAS FOR $1;
        ra_h   int;
        ra_m   int;
        ra_10s   int;
        ra_hs   varchar;
        ra_ms   varchar;
        ra_ss   varchar;

        absdec  float8;
        dec_d   int;
        dec_m   int;
        dec_s   int;
        dec_sign varchar;
        dec_ds   varchar;
        dec_ms   varchar;
        dec_ss   varchar;

        retval varchar:='';
    BEGIN
        ra_h := floor(ra_val);
        IF (ra_h < 10) THEN
            ra_hs := '0'||ra_h;
        ELSE
            ra_hs := ra_h;
        END IF;
        ra_m := floor(60.0*(ra_val - ra_h));
        IF (ra_m < 10) THEN
            ra_ms := '0'||ra_m;
        ELSE
            ra_ms := ra_m;
        END IF;
        ra_10s := round(36000*(ra_val-ra_h-ra_m/60.0));
        IF (ra_10s < 100) THEN
            ra_ss := '0'||floor(ra_10s/10)||'.'|| (ra_10s % 10);
        ELSE
            ra_ss := floor(ra_10s/10)||'.'|| (ra_10s % 10);
        END IF;


        IF ( (dec_val) < 0) THEN
            dec_sign := '-';
        ELSE
            dec_sign := '+';
        END IF;

        absdec := abs(dec_val);
        dec_d := floor(absdec);
        IF (dec_d < 10) THEN
            dec_ds := '0'||dec_d;
        ELSE
            dec_ds := dec_d;
        END IF;
        dec_m := floor(60.0*(absdec - dec_d));
        IF (dec_m < 10) THEN
            dec_ms := '0'||dec_m;
        ELSE
            dec_ms := dec_m;
        END IF;
        dec_s := round(3600*(absdec-dec_d-dec_m/60.0));
        IF (dec_s < 10) THEN
            dec_ss := '0'||dec_s;
        ELSE
            dec_ss := dec_s;
        END IF;

        retval := upper(eq_val) || ra_hs || ra_ms || ra_ss || dec_sign || 
                  dec_ds || dec_ms || dec_ss ;
        RETURN retval;
    END
$$ LANGUAGE 'plpgsql' IMMUTABLE;




----------------------------------------------------------------------------
-- Written by Dmitry Makarov 2017/01/19
-- hl_group returns list of galaxies which are in group with given object

DROP FUNCTION IF EXISTS hl_group( name VarChar ) ;
DROP FUNCTION IF EXISTS hlgroup( name VarChar ) ;
CREATE OR REPLACE FUNCTION hlgroup( name VarChar ) RETURNS SETOF a000 AS $$
DECLARE
  grp RECORD;
BEGIN
  SELECT 
    mg.grp_n AS n
  , mg.grp_ra AS ra
  , mg.grp_dec AS dec
  , mg.grp_rad AS r
  , mg.grp_cz AS cz
  , mg.grp_sigma AS sigma
  , mg.grp_cz-2*mg.grp_sigma AS cz_bot
  , mg.grp_cz+2*mg.grp_sigma AS cz_top
  , mg.grp_dec-mg.grp_rad AS dec_bot
  , mg.grp_dec+mg.grp_rad AS dec_top
  , mg.grp_rad/cos(mg.grp_dec/180.0*PI()) AS dra
  , (mg.grp_ra-mg.grp_rad/cos(mg.grp_dec/180.0*PI()))/15 AS rah_bot
  , (mg.grp_ra+mg.grp_rad/cos(mg.grp_dec/180.0*PI()))/15 AS rah_top
  INTO grp
  FROM m000_group AS mg
  WHERE mg.pgc=(SELECT m000.pgc FROM m000 WHERE objname=objname(name))
  LIMIT 1 ;
  
  IF grp.n IS NULL THEN
    RETURN;
  ELSEIF grp.dra>=180 THEN
    RETURN QUERY
    SELECT a000.*
    FROM a000
    WHERE
      a000.vvir BETWEEN grp.cz_bot AND grp.cz_top
      AND a000.de1950 BETWEEN grp.dec_bot AND grp.dec_top
      AND a000.objtype IN ('G','g','Q')
      AND angsep( a000.al1950*15, a000.de1950, grp.ra, grp.dec ) <= grp.r
    ;
  ELSEIF grp.rah_bot>0 AND grp.rah_top<=24 THEN
    RETURN QUERY
    SELECT a000.*
    FROM a000
    WHERE
      a000.vvir BETWEEN grp.cz_bot AND grp.cz_top
      AND a000.de1950 BETWEEN grp.dec_bot AND grp.dec_top
      AND a000.al1950 BETWEEN grp.rah_bot AND grp.rah_top
      AND a000.objtype IN ('G','g','Q')
      AND angsep( a000.al1950*15, a000.de1950, grp.ra, grp.dec ) <= grp.r
    ;
  ELSEIF grp.rah_bot<0 THEN
    RETURN QUERY
    SELECT a000.*
    FROM a000
    WHERE
      a000.vvir BETWEEN grp.cz_bot AND grp.cz_top
      AND a000.de1950 BETWEEN grp.dec_bot AND grp.dec_top
      AND ( a000.al1950<=grp.rah_top OR a000.al1950>=24+grp.rah_bot )
      AND a000.objtype IN ('G','g','Q')
      AND angsep( a000.al1950*15, a000.de1950, grp.ra, grp.dec ) <= grp.r
    ;
  ELSEIF grp.rah_top>24 THEN
    RETURN QUERY
    SELECT a000.*
    FROM a000
    WHERE
      a000.vvir BETWEEN grp.cz_bot AND grp.cz_top
      AND a000.de1950 BETWEEN grp.dec_bot AND grp.dec_top
      AND ( a000.al1950<=grp.rah_top-24 OR a000.al1950>=grp.rah_bot )
      AND a000.objtype IN ('G','g','Q')
      AND angsep( a000.al1950*15, a000.de1950, grp.ra, grp.dec ) <= grp.r
    ;
  ELSE 
    RAISE EXCEPTION 'Unsupported RA range (%,%).', grp.rah_bot, grp.rah_top ;
  END IF;
END;
$$ LANGUAGE plpgsql STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup000(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup000( name VarChar ) RETURNS SETOF a000 AS $$
SELECT * FROM hlgroup( $1 )
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup001(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup001( name VarChar ) RETURNS SETOF a001 AS $$
SELECT a.* FROM a001 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup002(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup002( name VarChar ) RETURNS SETOF a002 AS $$
SELECT a.* FROM a002 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup003(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup003( name VarChar ) RETURNS SETOF a003 AS $$
SELECT a.* FROM a003 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup004(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup004( name VarChar ) RETURNS SETOF a004 AS $$
SELECT a.* FROM a004 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup005(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup005( name VarChar ) RETURNS SETOF a005 AS $$
SELECT a.* FROM a005 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup006(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup006( name VarChar ) RETURNS SETOF a006 AS $$
SELECT a.* FROM a006 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup007(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup007( name VarChar ) RETURNS SETOF a007 AS $$
SELECT a.* FROM a007 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup008(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup008( name VarChar ) RETURNS SETOF a008 AS $$
SELECT a.* FROM a008 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup009(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup009( name VarChar ) RETURNS SETOF a009 AS $$
SELECT a.* FROM a009 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup010(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup010( name VarChar ) RETURNS SETOF a010 AS $$
SELECT a.* FROM a010 AS a LEFT JOIN hlgroup( $1 ) AS g USING (objname) WHERE g.objname IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup101(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup101( name VarChar ) RETURNS SETOF a101 AS $$
SELECT a.* FROM a101 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup102(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup102( name VarChar ) RETURNS SETOF a102 AS $$
SELECT a.* FROM a102 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup103(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup103( name VarChar ) RETURNS SETOF a103 AS $$
SELECT a.* FROM a103 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup104(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup104( name VarChar ) RETURNS SETOF a104 AS $$
SELECT a.* FROM a104 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup105(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup105( name VarChar ) RETURNS SETOF a105 AS $$
SELECT a.* FROM a105 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup106(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup106( name VarChar ) RETURNS SETOF a106 AS $$
SELECT a.* FROM a106 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup107(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup107( name VarChar ) RETURNS SETOF a107 AS $$
SELECT a.* FROM a107 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup108(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup108( name VarChar ) RETURNS SETOF a108 AS $$
SELECT a.* FROM a108 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup109(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup109( name VarChar ) RETURNS SETOF a109 AS $$
SELECT a.* FROM a109 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup110(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup110( name VarChar ) RETURNS SETOF a110 AS $$
SELECT a.* FROM a110 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup111(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup111( name VarChar ) RETURNS SETOF a111 AS $$
SELECT a.* FROM a111 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup112(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup112( name VarChar ) RETURNS SETOF a112 AS $$
SELECT a.* FROM a112 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup113(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup113( name VarChar ) RETURNS SETOF a113 AS $$
SELECT a.* FROM a113 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup114(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup114( name VarChar ) RETURNS SETOF a114 AS $$
SELECT a.* FROM a114 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

DROP FUNCTION IF EXISTS hlgroup115(VarChar) ;
CREATE OR REPLACE FUNCTION hlgroup115( name VarChar ) RETURNS SETOF a115 AS $$
SELECT a.* FROM a115 AS a LEFT JOIN hlgroup( $1 ) AS g USING (pgc) WHERE g.pgc IS NOT NULL ;
$$ LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT ;

-- end -----------------------------------------------------------------------
