The Postgres database has a procedural language called PL/PGSQL which can save you db round-trips and do all sorts of optimizations on the db layer. In this post I’m going to be showing 10 sql files as we progress on PL/PGSQL’s basics.
First here’s a basic hello world function:
-- hello-world.sql CREATE FUNCTION hello_world() RETURNS VOID AS $$ BEGIN RAISE NOTICE 'Hello World'; END; $$ LANGUAGE plpgsql;
Here we have a basic function with name hello_world, it returns nothing (VOID) and the statement body contains a single line which just raises the ‘Hello World’ text, its like the equivalent of echo or print if you will.
-- sum.sql CREATE FUNCTION sum(a INT, b INT) RETURNS INT AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;
Here we have a sum function which accepts two integers and also returns an integer. Notice how the parameters are declared, should be name then followed by its type. If your function returns something you also need to specify its type after RETURNS.
-- greet.sql CREATE OR REPLACE FUNCTION greet(name VARCHAR) RETURNS VARCHAR AS $$ BEGIN RETURN 'Hello ' || name; END; $$ LANGUAGE plpgsql;
The only thing that’s new here is the body, we concatenate strings using || operator.
-- swap.sql CREATE FUNCTION swap(a INT, b INT) RETURNS RECORD AS $$ DECLARE tmp INT; BEGIN tmp := a; a := b; b := tmp; RETURN (a, b); END; $$ LANGUAGE plpgsql;
Here we have the classic swap implementation, we declare variable via DECLARE block. Also we’re returning a set but the return type is RECORD. Its a special generic type which can hold any type, here we’re passing set to it.
-- status.sql CREATE OR REPLACE FUNCTION status(status INT) RETURNS VARCHAR AS $$ BEGIN IF status = 100 THEN RETURN 'DRAFT'; ELSIF status > 100 AND status < 400 THEN RETURN 'PROCESSING'; ELSIF status = 400 THEN RETURN 'RUNNING'; ELSIF status > 400 THEN RETURN 'STOPPED'; ELSE RETURN 'INVALID'; END IF; END; $$ LANGUAGE plpgsql;
This is how you do conditions, notice that equal comparison uses a single = (equal) sign.
-- query-single.sql CREATE OR REPLACE FUNCTION get_contact(contactid BIGINT) RETURNS contact AS $$ DECLARE result contact; BEGIN SELECT * INTO result FROM contact WHERE id = contactid; RETURN result; END; $$ LANGUAGE plpgsql;
This is our first example on how to use queries with it, we’re assigning the result on to result variable. Notice how the return type and result type is contact. This is the name of the table actually and if you use it as a type, it means that variable can hold a row type for that table.
-- result-existence.sql CREATE OR REPLACE FUNCTION get_contact(contactid BIGINT) RETURNS contact AS $$ DECLARE result contact; count INT; BEGIN SELECT * INTO result FROM contact WHERE id = contactid; GET DIAGNOSTICS count := ROW_COUNT; IF NOT FOUND THEN RAISE NOTICE 'not found!'; ELSE RAISE NOTICE 'found %!', count; END IF; RETURN result; END; $$ LANGUAGE plpgsql;
PL/PGSQL has a bunch of special variables and FOUND is just one of them. It holds a boolean value of wether or not our query returned anything. We can also retrieve the number of rows affected via ROW_COUNT which is another one of those special variables.
-- return-next.sql CREATE OR REPLACE FUNCTION get_numbers() RETURNS setof INT AS $$ DECLARE numbers INT = ARRAY[1, 2, 3]; BEGIN FOR i IN 1 .. array_upper(numbers, 1) LOOP RETURN NEXT numbers[i]; END LOOP; END; $$ LANGUAGE plpgsql;
Then we introduce arrays, you can do inline initialization by using ARRAY[…] form. One thing to keep in mind is that array’s index starts at 1 instead of 0, and you can get the number of elements on the built-in array function array_upper. We also introduce RETURN NEXT, what it does is it doesn’t actually exit the function but just appends the item to the implicit output handler of our function. Then once the function ends, it returns this altogether.
So what about queryset?
-- return-query.sql CREATE OR REPLACE FUNCTION get_contacts() RETURNS SETOF contact AS $$ BEGIN RETURN QUERY SELECT * FROM contact; END; $$ LANGUAGE plpgsql;
Here we introduce RETURN QUERY which lets us return a cursor to the queryset. Notice that the return type is SETOF contact, its required when using RETURN NEXT or RETURN QUERY as the rows returned will contain the set of columns for that table, contacts in this case.
-- cursor.sql CREATE OR REPLACE FUNCTION get_contacts() RETURNS SETOF contact AS $$ DECLARE pointer CURSOR FOR SELECT * FROM contact; BEGIN FOR row IN pointer LOOP RETURN NEXT row; END LOOP; END; $$ LANGUAGE plpgsql;
Finally as our last example, here’s an explicit usage of a bound cursor. Here pointer is a cursor pointing to its query’s result which we can iterate on a normal FOR loop. Again we use SETOF contact as return type to conform on the set of columns present on each row.
Note that while all we’ve shown are just stand alone functions, a function can call another one, can be called in a trigger and eveywhere else where a built-in postgres function can be called. Now go forth and try it!