Declaring and assigning values to variables in PostgreSQL

Question

First of all, I'm a total beginner in SQL. I have a table with 50+ columns, and now I'm doing calculations (on created temp table), but in some formulas, I got parameters, for example: A = 3

(A*(Column5 + Column7))/2

So, what is the best way to assign a value to a parameter?

This is what I was thinking about

DECLARE A          DOUBLE PRECISION:=3;

But I don't know how implementing it.

"assign a value to a parameter and use it later". I don't understand this. Are you asking about the scripting language? Are you asking about a way to define a function? When you are executing a single query, there is no "later". — Gordon Linoff
– Gordon Linoff, Commented Jul 20, 2017 at 10:58
My bad, sorry. I need to use it in a query or multiple queries, but I'm not sure if I need it assign for each query again or not. As I said, I'm a total beginner. I need help with calculating that formula with parameters, there would be everything from start to the end. — jovicbg
– jovicbg, Commented Jul 20, 2017 at 11:04

Gurmokh · Accepted Answer · 2017-07-20 14:42:48Z

1

The with option essentially creates a temp table that you can reference in a sql statement within the same transaction.

Your best bet is to create a function and then pass it the value of the parameter at run time. eg.

CREATE FUNCTION addColumns(
                          A integer, 
                          firstColumn integer, 
                          secondColumn integer
                            ) 
RETURNS integer
AS 
RETURN (A*(firstColumn + secondColumn))/2
LANGUAGE SQL 
IMMUTABLE;

Then use this in your query like:

 select addColumns(3, column5, column7) 
 from [table];

answered Jul 20, 2017 at 14:42

Gurmokh

2,09127 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pedreiro · Accepted Answer · 2017-07-20 11:13:33Z

0

As I could understand you want to store values using variables. This is already answered here : How to declare a variable in a PostgreSQL query

There are many solutions there, but I particularly like using a WITH clause as pointed in one of the answers, when using plain SQL. For more fancy things, you should write proper stored procedures.

edited Jul 20, 2017 at 11:13

answered Jul 20, 2017 at 11:05

Pedreiro

1,8342 gold badges19 silver badges30 bronze badges

Collectives™ on Stack Overflow

Declaring and assigning values to variables in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related