procname
: The name of the procedure.paramN
: The name of a parameter.typeN
: The type of paramN
.retval
: The return value.rettype
: The type of retval
.description.
Examples will appear when you click on the “Example” button.
In addition to the PostgreSQL standard aggregate functions, the following aggregate functions are available.
skewness
returns κ3/κ23/2,
where κi denotes the unbiased estimator of the i-th cumulant.
Note that the skewness such defined is biased.kurtosis
returns κ4/κ22,
where κi denotes the unbiased estimator of the i-th cumulant.
Note that the kurtosis such defined is biased.-- 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
returns the weight
ed mean of the value
s, namely
∑ivalue
i×weight
i / ∑iweight
i.
avg
calculates (∑value
/stddev
2)
/ (∑1
/stddev
2).chi2_per_freedom
calculates χ2 per degree of freedom,
which is defined as
(N-1)-1
∑ (value
- 〈value〉)2 / stddev
2.-- 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; ;
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
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).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].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).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;
date2mjd
converts a Gregorian date
'YYYY-MM-DD' (e.g. '2014-09-20')
to a modified Julian day.mjd2date
converts a modified Julian day
to a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20').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
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
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
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').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);
mag2flux
converts an AB magnitude to a flux
in erg/s/cm2/Hz.mag2fluxJy
converts an AB magnitude to a flux in Jansky.flux2mag
converts a flux
in erg/s/cm2/Hz
to an AB magnitude.fluxJy2mag
converts a flux in Jansky
to an AB magnitude.flux_cgs2Jy
converts a flux in erg/s/cm2/Hz
to a flux in Jansky.flux_Jy2cgs
converts a flux in Jansky
to a flux in erg/s/cm2/Hz.mag2flux_err
converts an AB magnitude error
to a flux error in erg/s/cm2/Hz.
The errors are standard deviations.mag2fluxJy_err
converts an AB magnitude error
to a flux error in Jansky.
The errors are standard deviations.flux2mag_err
converts a flux error in erg/s/cm2/Hz
to an AB magnitude error.
The errors are standard deviations.fluxJy2mag_err
converts a flux error in Jansky
to an AB magnitude error.
The errors are standard deviations.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
converts a flux error in Jansky
to a flux error in erg/s/cm2/Hz.
The errors are standard deviations.-- 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;
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
converts galactic coordinates (l
, b
)
to equatorial coordinates (ra
, dec
).
All the angles are in degrees.equ2gal
converts equatorial coordinates (ra
, dec
)
to galactic coordinates (l
, b
).
All the angles are in degrees.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) ;
HpxAng2pixNest
converts (ra
, dec
)
to a HEAPix pixel number in the NESTED scheme, with the given tessellation order
.HpxPix2angNest
converts a HEALPix pixel number to (ra
, dec
),
in the NESTED scheme with the given tessellation order
.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.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
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[IDStart
i, IDEnd
i).
This function calls Healpix_Base2::query_disc_inclusive
internally.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[IDStart
i, IDEnd
i).
This function calls Healpix_Base2::query_disc_inclusive
internally.visitCcd2frameId
converts a pair of a visit number and a ccd ID
(visit, ccd) to the corresponding frame ID (e.g. 'HSCA00000000').frameId2visitCcd
converts a frame ID (e.g. 'HSCA00000000')
to (visit, ccd).SELECT visitccd2frameid(2, 27); --return 'HSCA00000301' SELECT frameid2visitccd('HSCA00000301'); --return (2,27)
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
casts a double precision value x
to a single precision value
without under/overflow errors.c_multiply
computes x*y
without under/overflow errors.c_divide
computes x/y
without under/overflow errors.