Category: psql

005 Postgres Command Line : psql : Create a User Defined Function and Identify its creation and then Drop it.

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

004 Postgres Command Line : psql : Create a spatially enabled table

For this you will need to have a version of Postgres Database engine installed and running and you will need to have created a database which has the PostGis extension installed.

Open psql
Login to the database you wish to create the table in

type the following

CREATE TABLE t001landparcels (PKID SERIAL PRIMARY KEY, PARCELNAME VARCHAR(50), GEOM GEOMETRY(POLYGON,27700));

Here I do this and then check on the tables with the \dt command before inspecting the columns itself using the \d command.

and here I open up QGIS and link to my local postgres instance and the exampledb database;

and here I connect to it and draw a polygon. If you are wondering where it is this is InchKeith in the Firth of Forth and island very visible from George Street in Edinburgh. If you have flown into Edinburgh you will have flown almost over it.

and here after having digitised a single polygon I look at the contents of the table

SELECT count(*) FROM t001landparcels;

Produces the more helpful count of records in the table.

002 Postgres Command Line : psql : Enabling extensions (PostGIS) to a database

So you have an existing database in PostGres that you wish to add the PostGIS extension to.

You will need to be logged in with a username that has superuser privileges.
Here let me do this using the postgres default account that on my instance has superuser access.

Here rather than logging in at the start I am choosing to change the connection to a different database

This is done by using

\c DatabaseName;

or

\connect DatabaseName;

Please note that the semicolon is vital in performing the instruction.

The way I have discovered to do this is by opening the database and then allocating the postgis extension to the database once it is open.

Let us first examine the tables in our blank database before the extension is enabled.

\dt

Now let us enable the extension.

CREATE EXTENSION postgis;

Let us now examine the database tables

\dt

Enabling the PostGIS extension adds this table along with the ability to add geometry types of varaibles within tables.

001 Postgres Command Line : psql : Getting Started with Postgres

I am just getting into PostGres and here are some rough notes for my reference.

Assuming you have a postgres admin account you want to sign in first of all and create a database

To find the command line go to search in Windows and type psql

Ensure that your postgres engine is running firstly

You should be presented with the following

There are default connections to the local host keep hitting these initially until you reach the following;

You will now need to know your password enter it here and press return

I will deal with the warning message further in the post – but a lot of people experience this message so I wanted to keep it in at present.

From my initial investigations as ever it is a good idea to restrict users to particular privileges at this level I am not being particularly refined – I would like to have a poweruser role that I can allocate to people and give this a defined password.

Signing in you can check out the roles and users as follows – on starting up a new instance you may well see something like this \du

So here I try and set up a user called general and create a role called general which I give create DB rights

I would recommend something stronger than the classic password password.

Issuing the \du command again we get to see the roles

Now we can close down and go back in but this time login as username general by altering the appropriate item when asked.

Note how the =# characters have been replaced by => this appears to denote the non superuser sign in.

To identify what username you are logged in as type \c at the prompt

My investigations suggest that the # sign denotes that you are logged into the database as superuser.

So first of all lets get rid of that annoying warning message when you log in at psql

I am running Postgres version 9.5 your version may vary but you can remove the warning by editing runpsql.bat file every version of postgres has this file and it will be located in the version equivalent directory to 9.5 for me.

C:\Program Files\PostgreSQL\9.5\scripts

Add the line

cmd.exe /c chcp 1252

as per underline and save the file

Now fire up psql as usual you should get the following

It should be noted that if you REM out the SET statements you can choose login with particular server / localhost / database / port and username presets which may be useful if you find yourself constantly going into a particular database as a particular user.

Here you see that the warning note has stopped.

It should be noted that using the general username you will NOT be able to create Databases

In order to CREATE databases you will have to be signed in with a username with sufficient privileges here I am in as postgres and I create a database called ExampleDB

You can see that on carrying out a successful command we usually see a repeat of the command.

To get a list of all databases in the instance type

\l

It can be seen that despite typing the name in capitals the database has been created in lower case this seems to be a feature of commandline. If you desperately want capitals you may need to go to pgadmin tool.

I think I’ll end it there for now.