Character, Date and Number Functions.
There are some functions returning Character Values :
select INITCAP('mi nombre') as firt_upper , UPPER('only UppEr') as upper, LOWER('ALL LOwer') as lower;
Returns :
firt_upper | upper | lower ------------+------------+----------- Mi Nombre | ONLY UPPER | all lower
select POSITION('g' in 'Postgres') as "Where is the g in Postgres?";
Returns:
Where is the g in Postgres?
-----------------------------
5
Other Character functions include : CHAR_LENGTH, OCTET_LENGTH, SUBSTRING, TRIM, CHAR, LPAD, LTRIM, RPAD, RTRIM, SUBSTR, TEXT, TRANSLATE, VARCHAR
select ABS(-24) ,ABS(-414),ABS(-12112); RETURNS:
abs | abs | abs -----+-----+------- 24 | 414 | 12112
select ROUND(1.57),ROUND(1.57,1),ROUND(20.87,2); RETURNS:
round | round | round
-------+-------+-------
2 | 1.6 | 20.87
Other Number functions include: DEGREES, EXP, LN, LOG, PI, POW, RADIANS, ROUND, SQRT, CBRT, TRUNC, FLOAT, INTEGER, ACOS, ASIN, ATAN, ATNA2, COS, SIN, TAN.
There are also date functions: These deserve special attention because time handling is very different from Oracle's implementation. In Oracle you typically use DATE functions indicating the fields to be inculuded (YYYY-MM-DD HH24:MI:SS),these are some of the most used date functions and their syntax in Postgres:
SELECT abstime(timestamp 'now'); RETURNS:
abstime
------------------------
2001-04-02 15:11:26-04
and SELECT abstime(timestamp 'today'); RETURNS:
abstime
------------------------
2001-04-02 00:00:00-04
Notice that timestamp 'today' is truncated to complete days.
SELECT date_trunc('month',timestamp 'now'); performed on October 2nd RETURNS:
date_trunc
------------------------
2001-10-01 00:00:00-05
SELECT age('now','1975-07-30'); RETURNS:
age
----------------------------
25 years 8 mons 3 15:23:26
Other date functions: DATE_PART,ISFINITE,REALTIME,TO_CHAR
NOTE: Be aware of the data types you use on tables to manipulate dates and times,as there are various data types like: timestamp,date,interval, so if you try and insert a time interval ensure that your table column has a compatible data type to avoid errors or loss of information.
Unlike Oracle, Postgres (to the author's knowledge) does not provide other built-in (distribution) functions available in Oracle like: ADD_MONTHS,CONCAT and other single row functions, in order to provide this functionality you need to create your own SQL functions, these are not procedural functions in the strict 3GL sense(see previous benefits of PL/pgSQL ), they are just simple SQL constructs that will be available to users.An example that adds numbers:
CREATE FUNCTION suma(int4, int4) RETURNS int4
AS 'SELECT $1 + $2;' LANGUAGE 'sql';
This is very similar to an Oracle PL/SQL function, however notice the LANGUAGE specification 'sql'. To call the function from PSQL (~ Postgres's SQL*Plus):
SELECT suma(312,221) AS R; R ----- 533
If you see
Oracle's single row functions you can pretty much create functions like CONCAT and possibly more.
It is very probable that someone has already written a pretty concise library for built in function's which is not included in the distribution , very close to what Oracle provides.
It is worth mentioning that if you write your own functions and want to make them available to every database in a database system , they should be created inside the template1 database so every database at creation time will contain them.(More on this later)
Postgres does not provide this functionality. (See how Oracle executes this )
select * from parientes a,parientes b;
select * from mexico c,mexico y;
Note that Postgres supports the same "aliasing/shortcut" mechanism used in Oracle, using a letter after the table name.(Sql compliance with Oracle)
Postgres provides the AVG,SUM,MIN, MAX, COUNT which are equal to Oracle's functionality however with respect to other group functions,postgres (to the author's knowledge) does not provide any other built-in group (distribution) functions like STDDEV, VARIANCE.
Group functions return a single result based on many rows, as opposed to single-row functions.e.g The group COUNT function returns the number of rows returned. These functions are valid in the select list of a query and the GROUP BY clause ONLY.
Most functions can accept qualifiers as their arguments . These qualifiers are DISTINCT and ALL . If the DISTINCT qualifier is passed, then only distinct values returned by the query are considered. The ALL qualifier causes the function to consider all of the values returned by the query. If none is specified then ALL is the default.
select nombre,count(compras) from usuarios group by nombre;
select name,count(sales) from customers group by name;
select automoviles, avg(costo) "Total" from mis_automoviles group by automoviles having avg(costo) > 10000;
select cars, avg(cost) "Total" from my_cars group by cars having avg(cost) > 10000;
NOTE: In the previous example notice than in the having clause one cannot use the "alias name" Total used in the query, this is a restriction on the group by and having clauses , they cannot use aliased columns.
select modelo from automoviles group by modelo having color=rojo;
select model from cars group by model having color="red";
select * from usuarios group by edad having edad > 35 ;
select * from usuarios group by age having age > 35;