Return to the home page

Procedure List

Table of contents

Legend

procname(param1: type1, param2: type2) -> retval: retype
Types may be omitted.
Example
description.

Examples will appear when you click on the “Example” button.

Statistical functions

In addition to the PostgreSQL standard aggregate functions, the following aggregate functions are available.

skewness(value: double) -> skewness: double
skewness returns κ323/2, where κi denotes the unbiased estimator of the i-th cumulant. Note that the skewness such defined is biased.
kurtosis(value: double) -> kurtosis: double
kurtosis returns κ422, where κi denotes the unbiased estimator of the i-th cumulant. Note that the kurtosis such defined is biased.
Example
-- calculate the skewness of imag_sinc that are valid
SELECT
  skewness(imag_sinc) FILTER (WHERE -100 < imag_sinc AND imag_sinc < 100)
FROM
  pdr1_udeep.forced
WHERE
  detect_is_primary;

There are binary functions of the same type as above:

weighted_mean(value: double, weight: double) -> mean: double
weighted_mean returns the weighted mean of the values, namely ∑ivaluei×weighti / ∑iweighti.
avg(value: double, stddev: double) -> mean: double
avg calculates (∑value/stddev2) / (∑1/stddev2).
chi2_per_freedom(value: double, stddev: double) -> chi2_per_freedom: double
chi2_per_freedom calculates χ2 per degree of freedom, which is defined as (N-1)-1 ∑ (value - ⟨value⟩)2 / stddev2.
Example
-- calculate weighted mean of sinc magnitudes weighted by errors
SELECT
  avg(imag_sinc, imag_sinc_err) FILTER (WHERE c_isfinite(c_divide(imag_sinc, imag_sinc_err^2)) != 0)
FROM
  pdr1_udeep.forced
WHERE
  detect_is_primary;
;

Angle representation

dms2deg(dms: text) -> degrees: double
dms2deg converts a string in the format '+DD:MM:SS.SS' (e.g. '-87:65:43.21') to an angle in degrees. The angle ∈ [-90, 90].
hms2deg(hms: text) -> degrees: double
hms2deg converts a string in the format 'HH:MM:SS.SSS' (e.g. '12:34:56.789') to an angle in degrees. The input hour is multiplied by 15 to be the output angle. The hour ∈ [0, 24).
deg2dms(degrees: double) -> dms: text
dms2deg converts an angle in degrees to a string in the format '+DD:MM:SS.SS' (e.g. '-87:65:43.21'). The angle ∈ [-90, 90].
deg2hms(degrees: double) -> hms: text
hms2deg converts an angle in degrees to a string in the format 'HH:MM:SS.SSS' (e.g. '12:34:56.789'). The input angle is divided by 15 to be the output hour. The angle ∈ [0, 360).
Example
SELECT dms2deg('-01:00:12.00');
    --return -1.00333333333333

SELECT hms2deg('12:12:12.345');
    --return 183.0514375
SELECT deg2dms(83.0514375);
    --return +83:03:05.18

SELECT deg2hms(183.051416666667);
    --return 12:12:12.34
-- Get coordinates in hh:mm:ss.sss and +/-dd:mm:ss.ss rather than degrees:
SELECT
  deg2hms(ra ) AS ra,
  deg2dms(dec) AS dec
FROM
  pdr1_udeep.forced
LIMIT 10;

Date & time representation

date2mjd(date: text) -> mjd: int
date2mjd converts a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20') to a modified Julian day.
mjd2date(mjd: int) -> date: text
mjd2date converts a modified Julian day to a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20').
datetime2mjd(datetime: text) -> mjd: double
datetime2mjd converts a Gregorian date followed by a time 'YYYY-MM-DDThh:mm:ss.sss' (e.g. '2014-09-20T12:34:56.789') to a modified Julian day.
datetime2mjd(date: text, time: text) -> mjd: double
datetime2mjd converts a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20') and a time 'hh:mm:ss.sss' (e.g. '12:34:56.789') to a modified Julian day.
mjd2datetime(mjd: double) -> datetime: text
mjd2datetime converts a modified Julian day to a Gregorian date followed by a time 'YYYY-MM-DDThh:mm:ss.sss' (e.g. '2014-09-20T12:34:56.789').
mjd2datetime2(mjd: double) -> date: text, time: text
mjd2datetime2 converts a modified Julian day to a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20') and a time 'hh:mm:ss.sss' (e.g. '12:34:56.789').
Example
SELECT date2mjd('2014-07-17');

SELECT mjd2date(56855);
SELECT datetime2mjd('2014-07-17T12:12:12.000');

SELECT datetime2mjd('2014-07-17', '12:12:12.000');

SELECT mjd2datetime(56855.5084722222);

SELECT mjd2datetime2(56855.5084722222);

Flux and magnitude

mag2flux(mag: double) -> flux: double
mag2flux converts an AB magnitude to a flux in erg/s/cm2/Hz.
mag2fluxJy(mag: double) -> fluxJy: double
mag2fluxJy converts an AB magnitude to a flux in Jansky.
flux2mag(flux: double) -> mag: double
flux2mag converts a flux in erg/s/cm2/Hz to an AB magnitude.
fluxJy2mag(fluxJy: double) -> mag: double
fluxJy2mag converts a flux in Jansky to an AB magnitude.
flux_cgs2Jy(flux: double) -> fluxJy: double
flux_cgs2Jy converts a flux in erg/s/cm2/Hz to a flux in Jansky.
flux_Jy2cgs(fluxJy: double) -> flux: double
flux_Jy2cgs converts a flux in Jansky to a flux in erg/s/cm2/Hz.
mag2flux_err(mag: double, mag_err: double) -> flux_err: double
mag2flux_err converts an AB magnitude error to a flux error in erg/s/cm2/Hz. The errors are standard deviations.
mag2fluxJy_err(mag: double, mag_err: double) -> fluxJy_err: double
mag2fluxJy_err converts an AB magnitude error to a flux error in Jansky. The errors are standard deviations.
flux2mag_err(flux: double, flux_err: double) -> mag_err: double
flux2mag_err converts a flux error in erg/s/cm2/Hz to an AB magnitude error. The errors are standard deviations.
fluxJy2mag_err(fluxJy: double, fluxJy_err: double) -> mag_err: double
fluxJy2mag_err converts a flux error in Jansky to an AB magnitude error. The errors are standard deviations.
flux_err_cgs2Jy(flux_err: double) -> fluxJy_err: double
flux_err_cgs2Jy converts a flux error in erg/s/cm2/Hz to a flux error in Jansky. The errors are standard deviations.
flux_err_Jy2cgs(fluxJy_err: double) -> flux_err: double
flux_err_Jy2cgs converts a flux error in Jansky to a flux error in erg/s/cm2/Hz. The errors are standard deviations.
Example
-- Compare flux/magnitude in DB with those computed by the
-- conversion functions
SELECT iflux_sinc, mag2flux(imag_sinc), imag_sinc, flux2mag(iflux_sinc)
  FROM pdr1_udeep.forced
  LIMIT 10;
-- Select those objects that are brighter than 1e-6 Jy
SELECT iflux_sinc
FROM pdr1_udeep.forced
WHERE iflux_sinc > flux_Jy2cgs(1e-6)
LIMIT 10;

-- or,
SELECT imag_sinc
FROM pdr1_udeep.forced
WHERE imag_sinc < fluxJy2mag(1e-6)
LIMIT 10;

Galactic coordinates

The following are conversion functions from the galactic coordinates to the equatorial coordinates (J2000), and vice versa. The functions are based on SLALIB 2.5-4.

gal2equ(l: double, b: double) -> ra: double, dec: double
gal2equ converts galactic coordinates (l, b) to equatorial coordinates (ra, dec). All the angles are in degrees.
equ2gal(ra: double, dec: double) -> l: double, b: double
equ2gal converts equatorial coordinates (ra, dec) to galactic coordinates (l, b). All the angles are in degrees.
Example
SELECT gal2equ(230.0, 20.0);

SELECT equ2gal(120.0, 30.0);
-- Get galactic coordinates of objects which are
-- within 60 arcsec from the center at (gal-lon, gal-lat) =
-- (237, 42) in the forced catalog.

SELECT
  object_id, gal.lon, gal.lat, icmodel_mag
FROM
  pdr1_udeep.forced
  CROSS JOIN gal2equ(237, 42) AS equ(ra0, dec0)
  CROSS JOIN equ2gal(ra, dec) AS gal(lon, lat)
WHERE
  detect_is_primary
  AND coneSearch(coord, equ.ra0, equ.dec0, 60)
;

Healpix functions

HpxAng2pixNest(order: int, ra: double, dec: double) -> pixel: bigint
HpxAng2pixNest converts (ra, dec) to a HEAPix pixel number in the NESTED scheme, with the given tessellation order.
HpxPix2angNest(order: int, pixel: bigint) -> ra: double, dec: double
HpxPix2angNest converts a HEALPix pixel number to (ra, dec), in the NESTED scheme with the given tessellation order.
HpxQueryDiscNest(order: int, ra: double, dec: double, radius: double) -> SET OF pixel: bigint
HpxQueryDiscNest queries HEALPix pixels within a circle with the radius degrees centered at (ra, dec) degrees. The pixel numbering scheme is NESTED with the given tessellation order. This function calls Healpix_Base2::query_disc_inclusive internally.
HpxQueryBoxNest(order: int, ra: double, dec: double, width: double, height: double) -> SET OF pixel: bigint
HpxQueryDiscNest queries HEALPix pixels within a box with the size width degrees ×height degrees centered at (ra, dec) degrees. The pixel numbering scheme is NESTED with the given tessellation order. This function calls Healpix_Base2::query_polygon_inclusive internally.
HpxCoverCircleXyzNest(order: int, x: double, y: double, z: double, radius: double) -> SET OF IDStart: bigint, IDEnd: bigint
HpxCoverCircleXyzNest queries HEALPix pixels within a circle with the radius degrees centered at (x, y, z). The pixel numbering scheme is NESTED with the given tessellation order, shifted up to the 20th order. The pixels are returned in a set of spans — pixel ∈ i[IDStarti, IDEndi). This function calls Healpix_Base2::query_disc_inclusive internally.
HpxCoverCircleEqNest(order: int, ra: double, dec: double, radius: double) -> SET OF IDStart: bigint, IDEnd: bigint
HpxCoverCircleEqNest queries HEALPix pixels within a circle with the radius degrees centered at (ra, dec) degrees. The pixel numbering scheme is NESTED with the given tessellation order, shifted up to the 20th order. The pixels are returned in a set of spans — pixel ∈ i[IDStarti, IDEndi). This function calls Healpix_Base2::query_disc_inclusive internally.

HSC specific functions

visitCcd2frameId(visit: int, ccd: int) -> frameId: text
visitCcd2frameId converts a pair of a visit number and a ccd ID (visit, ccd) to the corresponding frame ID (e.g. 'HSCA00000000').
frameId2visitCcd(frameId: text) -> visit: int, ccd: int
frameId2visitCcd converts a frame ID (e.g. 'HSCA00000000') to (visit, ccd).
Example
SELECT visitccd2frameid(2, 27);
    --return 'HSCA00000301'

SELECT frameid2visitccd('HSCA00000301');
    --return (2,27)

Math functions

All math functions of the C programming language (functions in <math.h> of C11) are imported into the database, except for long double versions of functions (sinl(x), expl(x), etc.) They can be called with the prefix c_, e.g.

SELECT c_log10(-10), c_log10(0), c_log10(10);

c_log10(x), unlike the PostgreSQL native function log(x), does not raise error when x ≤ 0. Instead, c_log10(x) returns NaN or -Infinity in such cases.

Another example:

SELECT c_exp(1e100);

c_exp(x), unlike the PostgreSQL native function exp(x), does not raise error when the return value is overflowing. Instead, c_exp(x) returns +Infinity in such cases.

Single precision versions of functions are also available:

SELECT c_sinf(0.1), c_cosf(0.1);

Rather newly specified functions are available:

SELECT c_erf(1), c_tgamma(5), c_nextafter(0, 'infinity');

If a function has output parameters, a tuple is returned.

SELECT * FROM c_remquo(13, 5);
/* ret_value | quo
  -----------+-----
          -2 |   3
*/

(Note: double remquo(double x, double y, int *quo);

The first element of such a tuple is always named ret_value and the return value is stored in it.

The other elements are the output parameters, and are named the same way as they are in the C11 standard document.

Together with C11 functions, the following functions are available:

c_float(x: double) -> ret_value: real
c_float casts a double precision value x to a single precision value without under/overflow errors.
c_multiply(x: double, y: double) -> ret_value: double
c_multiply computes x*y without under/overflow errors.
c_divide(x: double, y: double) -> ret_value: double
c_divide computes x/y without under/overflow errors.