In this series we'll go over writing PLPGSQL stored functions. We shall follow up in a later issue with a one page cheat sheet.
All PLPGSQL functions follow a structure that looks something like the below.
CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text) RETURNS text AS $$ DECLARE strresult text; BEGIN strresult := ''; IF numtimes > 0 THEN FOR i IN 1 .. numtimes LOOP strresult := strresult || msg || E'\r\n'; END LOOP; END IF; RETURN strresult; END; $$ LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER COST 10; --To call the function we do this and it returns ten hello there's with carriage returns as a single text field. SELECT fnsomefunc(10, 'Hello there');
The basic make-up of a PLPGSQL function is as follows:
Note also the clause after the caching model is sometimes the words SECURITY DEFINER which means the function is run under the context of the owner of the function. This means the function can do anything the owner of the function has security to do even if the person running the function does not have those rights. This portion applies not just to PLPGSQL functions but any. If this clause is left out, then a function runs under the security context of the person running the function.
For users coming from SQL Server - this is similar in concept to SQL Server 2005 - EXECUTE AS OWNER (leaving Security definer out is equivalent to EXECUTE As CALLER in sql server). Note SQL Server 2005 has an additional option called EXECUTE As 'user_name' which PostgreSQL lacks that allows you to run under a named user that need not be the owner of the function.
For MySQL users, SECURITY DEFINER exists as well and works more or less the same as it does in PostgreSQL.
PLPGSQL has a couple of conditional logic structures. In the above we saw the simple IF THEN. There also exists IF .. ELSIF ..ELSIF END IF, IF ..ELSE ..END IF. We shall demonstrate by making dumb changes to our above.
CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text) RETURNS text AS $$ DECLARE strresult text; BEGIN strresult := ''; IF numtimes = 42 THEN strresult := 'Right you are!'; ELSIF numtimes > 0 AND numtimes < 100 THEN FOR i IN 1 .. numtimes LOOP strresult := strresult || msg || E'\r\n'; END LOOP; ELSE strresult := 'You can not do that. Please don''t abuse our generosity.'; IF numtimes <= 0 THEN strresult := strresult || ' You are a bozo.'; ELSIF numtimes > 1000 THEN strresult := strresult || ' I do not know who you think you are. You are way out of control.'; END IF; END IF; RETURN strresult; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; SELECT fnsomefunc(42, 'Hello there'); SELECT fnsomefunc(200, 'Hello there'); SELECT fnsomefunc(5000, 'Hello there');
In the above example we saw a variant of the FOR LOOP - below are a listing of the other basic control structures. In part 2 we shall delve into using some of these.
The basic control flow structures available in PLPGSQL are: