PostgreSQL, Variables

Question

I am using PostgreSQL through Npgsql driver for windows/NET and I see that it is possible to use PL/pgSQL language through it.
So that way I can make use of variables for my calculation scripts which may look like in this example:

DO $$
DECLARE 
   tlist    text='mylistofbills'; 
   tcontent text='mycontentofbills'; 

BEGIN 
CREATE TEMP TABLE tlist 
       (billno integer, bdate timestamp, rebate double precision) 
       ON COMMIT DROP;

INSERT INTO tlist 
VALUES (1, '10.01.2017. 10:14:56', 10),
       (2, '10.01.2017. 11:02:13', 5),
       (3, '10.01.2017. 11:45:22', 0),
       (4, '10.01.2017. 12:01:01', 6);

CREATE TEMP TABLE tcontent 
       (billno integer, rowno integer, price double precision, tax double precision) 
       ON COMMIT DROP;

INSERT INTO tcontent 
VALUES (1, 1, 100, 19),
       (1, 2, 30,   0),
       (2, 1, 20,  19),
       (3, 1, 18,  19),
       (4, 1, 43,   0);
END $$;

SELECT s.price,
       l.rebate,
       s.price/100*l.rebate AS valrebate, 
       s.price-(s.price/100*l.rebate) AS worebate, 
       ((s.price-(s.price/100*l.rebate))/100)*s.tax AS valtax,
       s.price-(s.price/100*l.rebate)+(((s.price-(s.price/100*l.rebate))/100)*s.tax) AS finalprice
  FROM tlist l, tcontent s 
 WHERE l.billno=s.billno;

Example is simplified (from real situation) and is suitable for pasting into PgAdmin's SQL editor.
So, now is question: Can I somehow in the body of those code, without adding new functions to server use formulas for writing more elegant and readable code?
If I would be able to add simple formulas like:

rebatec=s.price/100*l.rebate
priceworebate=s.price-rebatec

Then my code may look more readable and less error prone.
Like this:

SELECT s.price,
       l.rebate,
       rebatec AS valrebate, 
       priceworebate AS worebate, 
       (priceworebate/100)*s.tax AS valtax,
       priceworebate+((priceworebate/100)*s.tax) AS finalprice
  FROM tlist l, tcontent s 
 WHERE l.billno=s.billno;

If that may be possible where and how to put this formulas so it can be used in my last SELECT code?

SOLUTION: Based on @Clodoaldo's answer which give something new to me I find a solution which I am able to understand:

SELECT s.price, 
       l.rebate, 
       rebatec AS valrebate, 
       priceworebate AS worebate, 
       priceworebate/100*s.tax AS valtax, 
       priceworebate+priceworebate/100*s.tax AS finalprice 
  FROM tlist l, tcontent s, LATERAL
       (SELECT s.price/100*l.rebate AS rebatec, 
               s.price-s.price/100* l.rebate AS priceworebate 
       )sub 
 WHERE l.billno=s.billno;

It works and I hope it is technically correct.

Clodoaldo Neto · Accepted Answer · 2017-08-23 12:50:58Z

2

Use lateral:

The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list.

select 
    s.price,
    l.rebate,
    rebatec as valrebate, 
    priceworebate as worebate, 
    priceworebate / 100 * s.tax as valtax,
    priceworebate + priceworebate / 100 * s.tax as finalprice
from 
    tlist l
    inner join
    tcontent s using (billno)
    cross join lateral (
        select 
            s.price / 100 * l.rebate as rebatec,
            s.price - s.price / 100 * l.rebate as priceworebate
    ) cjl

Use the modern join syntax.

edited Aug 23, 2017 at 12:50

answered Aug 23, 2017 at 11:52

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Comments

Andomar · Accepted Answer · 2017-08-23 10:32:16Z

2

You could use a subquery to define those variables:

select  var1 * col3
from    (
        select  col1 / col2 as var1
        ,       *
        from    YourTable
        ) sub

Or alternatively a common table expression:

with    cte as
        (
        select  col1 / col2 as var1
        ,       *
        from    YourTable
        )
select  var1 * col3
from    cte

answered Aug 23, 2017 at 10:32

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Wine Too Over a year ago

Thanks. What would you suggest in mean of performance? Subquery or cte?

Collectives™ on Stack Overflow

PostgreSQL, Variables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related