Category: Postgres

As per 007 lets try and create our own function to calculate centigrade from fahrenheit and call it calcf

CREATE FUNCTION calcf(integer) RETURNS integer AS 'SELECT ($1 - 32) * 5/9' LANGUAGE SQL RETURNS NULL ON NULL INPUT;

Next lets see if I can write the same function using Python.

As per the nature of recursion a function is a variable is a function.

Previously in 005 and 006 we wrote functions that returned subsets of queries they were effectively dynamic queries where I entered a parameter that was used in a select query. This effectively meant that although the function was returning a variable this was a query of a select statement.

What if we wish just to return a single value as in say translate centigrade to fahrenheit or some other calcuation.

In such case you simply state the function should return a variable and you state the variable type.

eg

CREATE FUNCTION add(integer,integer) RETURNS integer
AS 'SELECT $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

There are a few interesting things here which should be born in mind

In 005 when last we left our intrepid explorers we were wondering if having defined an inline table that contains the definition of the selection purely in the user defined function how do we see what that selection is as it might not be a presently defined object. SetOF references an object we can execute independently of the function Table() – does not.

Well apparently magically you can run the following.

\ef public.getrecords

And a notepad will open with the UDF code in it.

Bright people think of everything.!

Like all platforms it is possible to create bespoke functions in Postgres

For the following I assume;
1. Postgres 9.5 is installed with the server running (syntax should be the same for other versions)
2. A database called exampledb has been created
3. In this database there exists table called t001landparcels with some records and a field called PKID
4. You are logged into the exampledb with a username called general that has been granted CREATEDB role.
5. You are in psql

The following can be used to create a simple function

CREATE FUNCTION getrecords(int) RETURNS SETOF t001landparcels AS $$
SELECT * FROM t001landparcels WHERE pkid <= $1;
$$ LANGUAGE SQL;

If you have been careful and done this exactly the same with my initial assumptions it should return

CREATE FUNCTION

This tells us that the functioned has been created this will now exist in the schema the table is as a permanent addition.

We can identify the function by either listing all the functions and scrolling through

\df public.*

or listing the individual function

\df public.getrecords

This should return your newly created function

Now to run the function – unlike MS Access you can’t simply run the function you need to allocate it to a select statement.

SELECT * FROM getrecords(2);

This should return everything you are looking for.

Now you should be able to drop the function using the following SQL

DROP FUNCTION getrecords(int);

Note how you have to define the function with its parameter I have read (no idea whether its true) that in version 10 of postgress you can simply use

DROP FUNCTION getrecords;

Writing code in psql does require accuracy so getting things to work does usually involve some experimentation. I have removed much of this from my screenshots!

An alternative is as follows

CREATE FUNCTION getrecords(int) RETURNS TABLE (pkid integer, parcelname text) as $$
SELECT pkid, parcelname FROM t001landparcels WHERE pkid <=$1;
$$ Language SQL;

This appears to result in the same answer I am not clear what the difference is yet – note the result would have been the same if I had defined the table with the additon of a geometry column.

Note I dropped the old getrecords function before I created this one. Not sure what would have happened if I had tried to create one over the other.

I found this second method in stackoverflow when investigating functions with the following to me slightly mysterious quote

This is effectively the same as using SETOF tablename, but declares the table structure inline instead of referencing an existing object, so joins and such will still work.

Which sounds to me as important but I’m struggling at present to understand its meaning!

Stackoverflow discussion

I will investigate later but what I think he is talking about is that you don’t need to have a defined object before hand eg in setof this is
SETOF t001landparcels

I quite often like to have things broken down rather than all in functions and it might prefer to have it as a setof something that I can more clearly see.

Which leads me to my next question how do you get the definition of a function see 006

This can be used by most roles either within a database or within the postgres instance.

\l

or

\list

Here I show that I am logged in with the general username.