In addition to the standard PostgreSQL & PostGIS functions, there are several Fulcrum-specific helper functions for formatting data and handling media attachments.
FCM_ConvertToFloat
FCM_ConvertToFloat(input_value text)
RETURNS double precision
FCM_ConvertToFloat('1.2') -- 1.2
FCM_ConvertToFloat('1000') -- 1000
FCM_ConvertToFloat('a') -- NULL
Convert a textual value to a floating point value. This function is similar to a cast, except it gracefully fails to NULL
when the input cannot be converted to a number. This is useful for text data that's mostly numbers but might have some invalid values in it.
FCM_FormatTimestamp
FCM_FormatTimestamp(ts timestamp without time zone, tz text DEFAULT 'UTC')
RETURNS text
Convert a timestamp to a different time zone. This is useful for localizing your timestamps to your own time zone. The tz
argument is a string representing the time zone to use. This string can be in any format supported by the PostgreSQL AT TIME ZONE
construct.
SELECT FCM_FormatTimestamp(current_timestamp, 'EST'); -- Eastern Standard Time
SELECT FCM_FormatTimestamp(current_timestamp, 'EDT'); -- Eastern Daylight-Saving Time
SELECT FCM_FormatTimestamp(current_timestamp, '-04:00'); -- 4 hours behind UTC
SELECT FCM_FormatTimestamp(current_timestamp, '-05:00'); -- 5 hours behind UTC
SELECT FCM_FormatTimestamp(current_timestamp, 'America/New_York'); -- New York time, either EST or EDT depending on the timestamp
The above examples show some of the ways the function can be used. The last form using America/New_York
is the most useful because it takes into account the changes in the timezones given the exact timestamp. For example, New York is sometimes on EST and sometimes on EDT.
FCM_Photo (single)
FCM_Photo(id text, version text DEFAULT NULL)
RETURNS fcm_file
id
The photo IDversion
(optional, defaultNULL
) The photo version. One of:NULL
(original)'large'
(jpg)'thumb'
(jpg)
Returns a photo URL in the output for a single photo.
The following will return a secure URL directly to the raw file, using its ID.
SELECT FCM_Photo('c515f1d6-e882-4027-9e5c-11e44b4c181c', 'thumb') AS photo_url;
The following will return a secure URL directly to the raw file for the second photo in a particular record.
SELECT FCM_Photo(photo_field[2], 'large') AS photo_url FROM "Building Inspections" WHERE _record_id='69daadc7-f68c-4c7c-8b40-7d9ea9e6d6c5';
The following will return secure URLs directly to the raw files for the first photos of all records in a table.
SELECT FCM_Photo(photo_field[1], 'thumb') AS photo_urls FROM "Building Inspections";
FCM_Photo (multiple)
FCM_Photo(ids text[], version text DEFAULT NULL)
RETURNS fcm_file[]
ids
An array of photo IDs, this works naturally with the way photo fields are stored.version
(optional, defaultNULL
) The photo version. One of:NULL
(original)'large'
(jpg)'thumb'
(jpg)
Returns an array of photo URLs in the output.
The following will return secure URLs directly to the raw files. The consumer of the output needs to be able to handle multiple URLs.
SELECT FCM_Photo(unnest(photo_field), 'thumb') AS photo_urls FROM "Building Inspections";
Here we use unnest
to expand an array to a set of rows.
FCM_Video (single)
FCM_Video(id text, version text DEFAULT NULL)
RETURNS fcm_file
id
The video IDversion
(optional, defaultNULL
) The video version. One of:NULL
(original)small
(mp4)medium
(mp4)preview_small
(gif)preview_medium
(gif)thumbnail_small
(jpg)thumbnail_medium
(jpg)thumbnail_large
(jpg)thumbnail_huge
(jpg)thumbnail_small_square
(jpg)thumbnail_medium_square
(jpg)thumbnail_large_square
(jpg)thumbnail_huge_square
(jpg)
Returns video URLs in the output for a single video.
The following will return a secure URL directly to the raw file.
SELECT FCM_Video(video_field[1]) AS video_url FROM "Building Inspections";
FCM_Video (multiple)
FCM_Video(ids text[], version text DEFAULT NULL)
RETURNS fcm_file[]
ids
The video IDsversion
(optional, defaultNULL
) The video version. One of:NULL
(original)small
(mp4)medium
(mp4)preview_small
(gif)preview_medium
(gif)thumbnail_small
(jpg)thumbnail_medium
(jpg)thumbnail_large
(jpg)thumbnail_huge
(jpg)thumbnail_small_square
(jpg)thumbnail_medium_square
(jpg)thumbnail_large_square
(jpg)thumbnail_huge_square
(jpg)
Returns video URLs in the output for multiple videos.
The following will return secure URLs directly to the raw video files.
SELECT FCM_Video(video_field) AS video_urls FROM "Building Inspections";
FCM_Audio (single)
FCM_Audio(id text, version text DEFAULT NULL)
RETURNS fcm_file
id
The audio IDversion
(optional, defaultNULL
) The audio version. One of:NULL
(original)small
(m4a)medium
(m4a)
Returns audio URLs in the output for a single audio file.
The following will return a secure URL directly to the raw audio file.
SELECT FCM_Audio(audio_field[1]) AS audio_url FROM "Building Inspections";
FCM_Audio (multiple)
FCM_Audio(ids text[], version text DEFAULT NULL)
RETURNS fcm_file[]
ids
The audio IDsversion
(optional, defaultNULL
) The audio version. One of:NULL
(original)small
(m4a)medium
(m4a)
Returns audio URLs in the output for multiple audio files.
The following will return secure URLs directly to the raw audio files.
SELECT FCM_Audio(audio_field) AS audio_urls FROM "Building Inspections";
FCM_Signature
FCM_Signature(id text, version text DEFAULT NULL)
RETURNS fcm_file
id
The signature IDversion
(optional, defaultNULL
) The signature version. One of:NULL
(original)'large'
(png)'thumb'
(png)
Returns a signature URL in the output for a single signature.
The following will return secure URLs directly to the raw signature files.
SELECT FCM_Signature(signature_field) AS signature_url FROM "Building Inspections";