How do I use a variable in Postgres scripts?

Question

I'm working on a prototype that uses Postgres as its backend. I don't do a lot of SQL, so I'm feeling my way through it. I made a .pgsql file I run with psql that executes each of many files that set up my database, and I use a variable to define the schema that will be used so I can test features without mucking up my "good" instance:

\set schema_name 'example_schema'

\echo 'The Schema name is' :schema_name

\ir sql/file1.pgsql
\ir sql/file2.pgsql

This has been working well. I've defined several functions that expand :schema_name properly:

CREATE OR REPLACE FUNCTION :schema_name.get_things_by_category(...

For reasons I can't figure out, this isn't working in my newest function:

CREATE OR REPLACE FUNCTION :schema_name.update_thing_details(_id uuid, _details text)
    RETURNS text
    LANGUAGE 'plpgsql'
AS $BODY$

BEGIN
    UPDATE :schema_name.things
    ...

The syntax error indicates it's interpreting :schema_name literally after UPDATE instead of expanding it. How do I get it to use the variable value instead of the literal value here? I get that maybe within the BEGIN..END is a different context, but surely there's a way to script this schema name in all places?

Answer 1

I can think of three approaches, since psql cannot do this directly.

Shell script

Use a bash script to perform the variable substitution and pipe the results into psql, like.

#!/bin/bash

$schemaName = $1
$contents = `cat script.sql | sed -e 's/@SCHEMA_NAME@/$schemaName'`

echo $contents | psql

This would probably be a lot of boiler plate if you have a lot of .sql scripts.

Staging Schema

Keep the approach you have now with a hard-coded schema of something like staging and then have a bash script go and rename staging to whatever you want the actual schema to be.

Customize the search path

Your entry point could be an inline script within bash that is piped into psql, does an up-front update of the default connection schema, then uses \ir to include all of your .sql files, which should not specify a schema.

#!/bin/bash

$schemaName = $1

psql <<SCRIPT
SET search_path TO $schemaName;
\ir sql/file1.pgsql
\ir sql/file2.pgsql
SCRIPT

Some details: How to select a schema in postgres when using psql?

Personally I am leaning towards the latter approach as it seems the simplest and most scalable.

Answer 2

The documentation says:

Variable interpolation will not be performed within quoted SQL literals and identifiers. Therefore, a construction such as ':foo' doesn't work to produce a quoted literal from a variable's value (and it would be unsafe if it did work, since it wouldn't correctly handle quotes embedded in the value).

Now the function body is a “dollar-quoted%rdquo; string literal ($BODY$...$BODY$), so the variable will not be replaced there.

I can't think of a way to do this with psql variables.