Bookmarks
Account
ACL
Tools
New
Other
New. Function/Trigger - (Easy is a Relative Term)
Pre written, built-in Functions
A Function is a 'shortcut' for an 'automatic calculation or result'. An example is the function: TRIM( ). If you used TRIM, it would remove all the leading and trailing spaces from some text. So: TRIM(address) would get rid of all the spaces before the 1st character and after the last character. " 123 Anywhere Lane " would be automatically 'fixed' and become "123 Anywhere Lane".
Trim is a Standard function and 'prewritten'.
Younicycle gives you the means to create your OWN Functions & Triggers as we include a special interface for 'plpgsql'. See an image of the interface at the bottom of this page.
Trim is a Standard function and 'prewritten'.
Younicycle gives you the means to create your OWN Functions & Triggers as we include a special interface for 'plpgsql'. See an image of the interface at the bottom of this page.
Your Younicycle account includes 'standard functions' that are pre-written, but also allows you to create your own functions. If it takes 5 minutes to write a calculation - and - variations of this calculation will be created many times in the future, then it will be a time-saver to create a function that will run your calculations automatically without a lengthy 'rewrite'.
This level of flexibility and power is far beyond what can typically be found on the web and is an advanced feature that may need the help of an Expert. Thankfully, YC is Collaborative.
This level of flexibility and power is far beyond what can typically be found on the web and is an advanced feature that may need the help of an Expert. Thankfully, YC is Collaborative.
avg(arg1)count(arg1)min(arg1)max(arg1)sum(arg1)bit_and(expression)bit_or(expression)bool_and(expression)bool_or(expression)every(expression)
abs(arg1)acos(arg1)asin(arg1)atan(arg1)ceil(arg1)cos(arg1)cot(arg1)degrees(arg1)exp(arg1)floor(arg1)ln(arg1)log(arg1)pi()pow(arg1, arg2)radians(arg1)round(arg1)sin(arg1)sqrt(arg1)tan(arg1)
convert(arg1, [src_encoding name,] dest_encoding_name)lower(arg1)upper(arg1)length(arg1)md5(arg1)replace(arg1, from, to)substr(arg1, from[, count])trim(arg1)
age(arg1)cur_date()cur_datetime()cur_time()get_periods(from_interval [, start_date[, actual_date]])
if(cond_expr, true_expr, false_expr)COALESCE(value [, ...])NULLIF(value1, value2))GREATEST(value [, ...])LEAST(value [, ...])
to_char(arg1,frmt_str)to_date(arg1,frmt_str)to_number(arg1,frmt_str)
array_append(anyarray, anyelement)array_cat(anyarray, anyarray)array_dims(anyarray)array_lower(anyarray, int)array_prepend(anyelement, anyarray)array_to_string(anyarray, text)array_upper(anyarray, int)string_to_array(text, text)
corr(Y, X)covar_pop(Y, X)covar_samp(Y, X)regr_avgx(Y, X)regr_avgy(Y, X)regr_count(Y, X)regr_intercept(Y, X)regr_r2(Y, X)regr_slope(Y, X)regr_sxx(Y, X)regr_sxy(Y, X)regr_syy(Y, X)stddev(expression)stddev_pop(expression)stddev_samp(expression)variance(expression)var_pop(expression)var_samp(expression)
di_moneylast(S, E, r)di_canspend(E, r, n)ii_moneylast(S, E, r, ss)ii_canspend(E, r, n, ss)
chart(name)barcode(code,fgcolor,bgcolor)image(imagename,folder,schema)
FV(pv double precision, i double precision, n double precision)yc_int_2(f_start integer, f_interval integer)yc_interval(f_start integer, f_interval integer, seq_length integer, elm_idx integer)
Aggregates
Math
Array
Conditional
Statistics
Container
String
Format
User Defined
Financial
Date Time
More examples will be added to the Site as time permits. Until then, to read more about Functions and Triggers, go to:
PostgreSQL manual
CREATE
Additional screen space for code not displayed
Functions - for most of us, will be a Task for Experts.
An expert may be able to create the Function you need in an hour or less. So - you may have done the great majority of the work - but need help on one 'snippet of code'.
This is one more reason why Collaboration is useful and, for most of us, essential.
An expert may be able to create the Function you need in an hour or less. So - you may have done the great majority of the work - but need help on one 'snippet of code'.
This is one more reason why Collaboration is useful and, for most of us, essential.
If you are proficient with PGSQL, please let us know via the Contact form.
We will be setting up a means for Novices to Find Experts - and work together on Projects.
We will be setting up a means for Novices to Find Experts - and work together on Projects.
New Function
1
2
New Trigger
Example: sequencer (set up as below)
Function name: sequencerDescription: autogenerates a sequence of numbersFolder: RootVisibility: PrivateFunction Type: FunctionParams: seq_start integer, seq_step integer, seg_run integerReturn: IntegerSETOF (check this box)
CREATE
DECLARE run_num integer; ret_num integer; BEGINrun_num := 0;
WHILE run_num < seq_run LOOP ret_num := seq_start + run_num*seq_step; run_num := run_num +1; RETURN NEXT ret_num; END LOOP; RETURN;
END;
WHILE run_num < seq_run LOOP ret_num := seq_start + run_num*seq_step; run_num := run_num +1; RETURN NEXT ret_num; END LOOP; RETURN;
END;
copy & paste the code below into the scripting editor. Only this code should appear.
You will now be able to use this Function in Step 2 of your Queries (Relations). It will appear as a list of Set returning functions -- just below your Tables. See below
Then ....
Click
Type in your parameters, then click Build SQL
Click
Your sequence
ARGH!.
DISTINCT was NOT necessary -- but it won't hurt the results.
DISTINCT was NOT necessary -- but it won't hurt the results.