007 Postgres Command Line : psql : Create a function that returns a single value

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 this case the addition is performed in SQL
  • I have to specifically name the language of the calculation (SQL) which suggests that if you stated another language it might accept it!
  • You still need to select the function to run it. This indicates that postgres 9.5 doesn’t executve functions as per some environments (ms access for example) – I have read that version 11 changed this and allows you to execute or perform a function. Ms Access you don’t even need to write execute simply the name of the function with the integers.
  • Variables are refererred to as to their input position unlike VBA where you dimension the variable and give it a name. I am unclear at the moment the advantages of the former or later but it is interesting nonetheless. I first came across something similar with autohotkey.
  • About Mark

    Mark Brooks a forty something individual working and living in and around Edinburgh
    This entry was posted in All, PostGIS, Postgres, psql. Bookmark the permalink.