Instructions for using PostgreSQL SQL statement variables

Time:2022-7-12

We use general variables in functions. Here we develop requirements. We need to use variables directly in SQL to find some problems, such as time variables, which should be filtered according to time

There are three ways to achieve this

1. PSQL command uses variables

The data in the table are as follows:


hank=> select * from tb2;
 c1 | c2  |       c3       
----+-------+----------------------------
 1 | hank | 2018-02-06 10:08:00.787503
 2 | dazui | 2018-02-06 10:08:08.542481
 3 | wahah | 2018-02-06 10:08:15.468527
 4 | aaaaa | 2018-02-06 10:18:39.289523

The SQL text is as follows


cat hank.sql 
select * from tb2 where c2=:name and c3>=:time;

View through PSQL


psql -v name="'hank'" -v time="'2018-02-06 10:08:00'" -f hank.sql
 c1 | c2 |       c3       
----+------+----------------------------
 1 | hank | 2018-02-06 10:08:00.787503

perhaps


 psql -v name="'hank'" -v time="'2018-02-06 10:08:00'" -c '\i hank.sql'
 c1 | c2 |       c3       
----+------+----------------------------
 1 | hank | 2018-02-06 10:08:00.787503

Same effect

2. \set use variables


hank=> \set name hank
hank=> \set time '2018-02-06 10:09:00'  
hank=> select * from tb2 where c2=:'name' and c3>=:'time';
 c1 | c2 |       c3       
----+------+----------------------------
 1 | hank | 2018-02-06 10:08:00.787503

3. By defining parameters

Set a session level parameter through current_ Setting value


hank=> set session "asasd.time" to "2018-02-06 10:09:00"; 
SET
hank=> select * from tb2 where c3 >= current_setting('asasd.time')::timestamp;
 c1 | c2  |       c3       
----+-------+----------------------------
 4 | aaaaa | 2018-02-06 10:18:39.289523
(1 row)

Supplement: PostgreSQL storage functions / stored procedures use SQL statements to assign values to variables

--Define variables
a numeric;

Method 1:

select sqla into a from table1 where b = '1' ;  -- This is the SQL statement assignment

Mode 2:

sql1:= 'select a from table1 where b = ' '1' ' ';
execute sql1 into a; -- This is the execution of storage function assignment

The above is my personal experience. I hope I can give you a reference, and I hope you can support developeppaer. If there is any mistake or failure to consider completely, please don’t hesitate to comment.