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
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
or listing the individual function
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
SETOFtablename, 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!
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
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