Product Documentation

Appearance

PREPARE ​

Prepare a SQL statement for execution with the EXECUTE command and define parameters that you supply on execution.

PREPARE name [ ( data_type [, ...] ) ] AS statement

Prepared statements exist only for the duration of a session. (To remove prepared statements within a session, use the DEALLOCATE command.)

data_type
Define the data type for each parameter. See SQL Data Types for information about supported types. Refer to the parameters in the prepared statement by using variables of the form $1, $2, and so on.
statement
Use any supported SELECT, INSERT, UPDATE, or DELETE statement. Parameters are not supported in GROUP BY and ORDER BY clauses.

For example, this prepared query refers to a parameter for the season_name column twice (once in each subquery):

prepare goals(varchar(9)) as
select a.name, homegoals, awaygoals, 
homegoals+awaygoals as goals_for
from 
(select t1.name, sum(substr(ftscore,1,1)::int) 
from match m1, team t1, season s1
where (m1.htid=t1.htid and m1.seasonid=s1.seasonid)
and season_name=$1
group by t1.name) as a(name,homegoals),
(select t2.name, sum(substr(ftscore,3,1)::int) 
from match m2, team t2, season s2
where (m2.atid=t2.atid and m2.seasonid=s2.seasonid)
and season_name=$1
group by t2.name) as b(name,awaygoals)
where a.name=b.name
order by 4 desc;
PREPARE
premdb=# execute goals('1999-2000');
       name         | homegoals | awaygoals | goals_for 
---------------------+-----------+-----------+-----------
 Manchester United   |        59 |        38 |        97
 Arsenal             |        42 |        31 |        73
 Newcastle United    |        42 |        21 |        63
 Everton             |        36 |        23 |        59
 Leeds United        |        29 |        29 |        58
 Tottenham Hotspur   |        40 |        17 |        57
 Sunderland          |        28 |        29 |        57
 Leicester City      |        31 |        24 |        55
 Chelsea             |        35 |        18 |        53
 West Ham United     |        32 |        20 |        52
 Liverpool           |        28 |        23 |        51
 Coventry City       |        38 |         9 |        47
 Middlesbrough       |        23 |        23 |        46
 Aston Villa         |        23 |        23 |        46
 Wimbledon           |        30 |        16 |        46
 Southampton         |        26 |        19 |        45
 Derby County        |        22 |        22 |        44
 Sheffield Wednesday |        21 |        17 |        38
 Bradford City       |        26 |        12 |        38
 Watford             |        24 |        11 |        35
(20 rows)
premdb=# deallocate goals;
DEALLOCATE
premdb=# execute goals('2003-2004');
ERROR:  prepared statement "goals" does not exist
premdb=#

Parent topic:SQL Commands