Easily change the dates and times on your photos and videos
Click here for more info about the app
English dictionary with notifications so you won't forget what you're studying!
Click here for more info about the app
The app that quizzes and scores you on your vocabulary!
Click here for more info about the app

How to Use Named Variables with Postgres and PHP PDO Driver


You can write reusable scripts with Postgres by taking advantage of named variables.

A named variable starts with a : in your sql script. Here is an example

select :a_number

You can then use this statement with psql and the --variable flag

echo "select :a_number::integer a_number" | psql --variable 'a_number=123'
(1 row)

If you have larger, more complicated sql statements you can save them in a file and cat the file to psql instead.

cat my_script.sql | psql --variable 'a_number=123'

If you're using PHP and PDO you can use prepared statements to bind variables.

$q = $db->prepare(file_get_contents('my_script.sql'));
$q->execute([':a_number' => '1']) or die(json_encode($q->errorInfo()));
while($r = $q->fetch()){ var_dump($r); }

This makes it easy to separate your SQL from your PHP code and run the same scripts from the command line.

Tagged w/ #php #pdo #postgres #variables #sql #software #psql