Declaring mathematical expressions as variables in Postgres SQL functions

user_15402004 0
Viewed 220

I was wondering if I could get some help with declaring mathematical expressions as variables in pl/pg sql.

When creating a function the general format is :

CREATE OR REPLACE FUNCTION [insert function name, input parameters and data type]

RETURNS NUMERIC

LANGUAGE plpgsql

AS

$$

  DECLARE
       mathEX NUMERIC;  — example variable

BEGIN

—-mathematical operation—-

RETURN math_example;
END;
$$;

My question is if the purpose of my function is to create a mathematical operation but the declared variables are sub mathematical expressions to help build the primary math expression then should I store the “sub math expressions” with the variable under the “DECLARE”section? Or should they be listed first in the “BEGIN” section?

For example,

Should it be

DECLARE
  X NUMERIC(8,6) : = Z/36;

Or....

DECLARE 
   X NUMERIC(8,6);

BEGIN
   X := Z/36;

—- then insert primary math formula—-
plpgsql postgresql sql
3 Answers

You can use mathematical expression in DECLARE section as well as in BEGIN section. But any variable can be declared in DECLARE section only. You can not declare it after BEGIN.

See the example below:

create or replace function test( z int)
returns numeric as
$$
declare
x numeric(8,4):=round(z*4.1234567,2);
begin

return x;
end;

$$
language plpgsql

DEMO

user_77724830

Both forms are equivalent. Choose the one that makes the function easy to read and maintain.

user_64643080

The formats are very similar, but not the same. There is a difference in error handling on initialization. If the expression is in the Declaration section and an error occurs it will not be caught by the Exception section; it is immediate thrown to the calling block or statement. If the same error would occur in the Execution section it will be caught in the Exception section and processed there. See demo.

user_76238560
Related