How to Print Variable in PostgreSQL
Before printing the variable, we need to declare it in the Postgres function or query. Then, we can use raise notice to print the value of those variables.
Use raise notice to Print Variable Value in PostgreSQL
Let’s say we have the following table containing the product information.
postgres=# select * from product;
product_id | product_name | entered_by | product_price | product_stock
------------+----------------------------------+------------+---------------+---------------
1 | Mustard - Dijon | 10 | 57.44 | 155
2 | Bacardi Limon | 6 | 51.68 | 395
3 | Chicken - Leg, Fresh | 6 | 57.88 | 130
4 | Mcgillicuddy Vanilla Schnap | 9 | 45.76 | 130
This table contains fifty product lists. Now, let’s say we want to print the average price of the products in a formatted string.
So the PL/SQL query will be:
do $$
declare
average product.product_price%type;
begin
select avg(product_price)
from product
into average;
raise notice 'Average price is %s', average;
end; $$;
NOTICE: Average price is 40.36s
Print Multiple Variable Values in PostgreSQL
Let’s say you want to see the average product price and total stock for each id. For example, we want to print like ID 1 entered 400 product, and the average price is 50.
Here’s the command to do this:
do $$
declare
average product.product_price%type;
stock product.product_stock%type;
id product.entered_by%type;
begin
select avg(product_price),sum(product_stock),entered_by
from product
into average,stock,id
WHERE entered_by=1
GROUP BY ENTERED_BY;
raise notice 'ID %s entered a total of % products, and the average price is %',id, stock, average;
end; $$;
NOTICE: ID 1s entered a total of 1111 products, and the average price is 41.75
DO
postgres=#
So, here’s how we can print multiple variables from the Postgres query. We can use this raise notice in functions or inside the DO.
You can also use the loop and print each row if you want. To know more about the print function of the variables, visit the official documentation from here.
