0

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.

user272735
  • 10,473
  • 9
  • 65
  • 96
jovicbg
  • 1,523
  • 8
  • 34
  • 74
  • "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 Jul 20 '17 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 Jul 20 '17 at 11:04

2 Answers2

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];
VynlJunkie
  • 1,953
  • 22
  • 26
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.

Pedreiro
  • 1,641
  • 2
  • 18
  • 28