Functions in Postgres SQL vs Functions in MySQL
Postgres SQL - Functions:
In SQL Server and MySQL use a proprietary extension of SQL to provide stored procedure functionality (amongst other features). Microsoft calls it Transact SQL, or “T-SQL”, and as far as I know, MySQL just calls it “MySQL”. Both variants are different enough to occasionally trip up a developer but are written around the common SQL-92/2003 standards. The net result is that the syntax used inside stored procedures resembles SQL code used without sprocs; the same “SELECTs”, “INSERTs”, etc. but with a few extra commands added for control flow and the like. Postgres stored procedures can also be written with a SQL-like language called “PL/pgSQL”.
But, as an added twist, a developer could instead chose to write it in Perl, Python, or Tcl using PL/Perl, PL/Python, or PL/Tcl. Perl/Python/Tcl hackers may rejoice at this thought and it does open up a great many possibilities.
CREATE FUNCTION Orders_details(order_id int) RETURNS int AS $$
DECLARE
qty int;
BEGIN
SELECT COUNT(*) INTO ord
FROM Orders
WHERE oid= order_id;
RETURN ord;
END;
$$ LANGUAGE plpgsql;
The mentionable differences (besides the self-explanatory LANGUAGE command):
Microsoft SQL Server has distinct roles for both user-defined functions (UDFs) and stored procedures. A UDF is typically used within the context of a query, while a sproc takes input and/or output from/to an application. MySQL also has both procedures and functions, but they are defined almost identically and work almost identically. Postgres dispenses with the difference and everything is simply defined as a “function”. As with MySQL, we also see the double dollar-sign (”$$”). However, here we’re using it for a different kind of work-around. With Postgres, the function definition is just one long string and if single quotes are contained somewhere within it,
they’d have to be escaped using a pair of single quotes: ”. If there were already escaped quotes within the function, they’d then need to be escaped again, giving us a total of four quotes.
To make life easier (and our code more legible), we can surround the definition with double dollar signs which negates the need for escaping.
Calling the function is also a bit different:
SELECT Orders_details(17);
By the same way we can create trigger procedures in postgres sql by return as trigger.
In this we call this function, each time that particular trigger is executing.
Like mysql, we can create the triggers on before/after insert or delete or update a table.
we can create trigger as follows:
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_check();
And function is as follows:
CREATE FUNCTION emp_check() RETURNS trigger AS $emp_stamp$
BEGIN
IF NEW.empname IS NULL THEN
RAISE EXCEPTION ‘empname cannot be null’;
END IF;
END;
RETURN NEW;
$emp_stamp$ LANGUAGE plpgsql;
