Written by Sean Behan on Sat Nov 11th 2017

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 with..
#php #pdo #postgres #variables #sql #software #psql

Just finishing up brewing up some fresh ground comments...