Use psql --set variable in a function

Question

I want to create a script that will create some custom named schema and within it some tables and functions. Like this:

The example.sh file:

#!/bin/bash
# this is the only place I want to set the schema name
SCH="ex"
export SCH
export PGPASSWORD="*******"
PSQL="psql \
 -X \
 -U postgres \
 -h localhost \
 --single-transaction \
 --echo-all \
 --set SCH=$SCH \
 --set ON_ERROR_STOP=on "
eval $PSQL "-f ./example.sql"

The example.sql file:

DROP SCHEMA IF EXISTS :SCH CASCADE;
CREATE SCHEMA :SCH;
CREATE TABLE :SCH.my_table
(
 id SERIAL PRIMARY KEY,
 my_col text
);
INSERT INTO :SCH.my_table (my_col) VALUES ('abc'), ('def');
CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;
SELECT * FROM :SCH.getLast();

It works fine until the:

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

As the function body is a text constant, the :SCH is not substituted by the schema name and we get an error:

psql:./example.sql:16: ERROR: syntax error at or near ":"
LINE 3: SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;

Is there a neat way to make it work?

Workaround

This can be done in the following way, but it seems there should be an easier way:

CREATE OR REPLACE FUNCTION :SCH.makeFunction(schema_name text)
RETURNS VOID AS $body$
BEGIN
 EXECUTE format('CREATE OR REPLACE FUNCTION %1$s.getLast()
 RETURNS text AS $$
 SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
 $$ LANGUAGE sql STABLE;', schema_name);
END
$body$ LANGUAGE plpgsql VOLATILE;
SELECT :SCH.makeFunction(:'SCH');

Answer 1

An interesting way of achieving this is using a psql variable to store the whole function body, like

\set body '$$SELECT 1$$'
CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS :c;
SELECT bla();
 bla 
─────
 1

Alternatively, you can pass the whole definition into the variable, and then run it:

\set function 'CREATE FUNCTION bla() RETURNS integer LANGUAGE SQL AS $$SELECT 1;$$;'
:function
SELECT bla();
 bla 
─────
 1

So, the question is how to put the body into a suitable variable.

From psql 9.3 above, there is the \gset command, that comes to the rescue. We build a query that produces the function body as an output, then assign it to a psql variable, and use it like above (with format()):

SELECT format('CREATE OR REPLACE FUNCTION %1$s.getLast()
 RETURNS text AS $$
 SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
 $$ LANGUAGE sql STABLE;', 'test') AS function;
\gset
:function

And done.

Answer 2

As an alternative to prefixing all objects with the schema-qualifier :SCH variable, you may assign search_path, so that it happens automatically.

CREATE SCHEMA :"SCH";
SET search_path TO :"SCH";

After this, all CREATE statements and type lookups happen in :SCH.

Concerning queries inside the bodies of functions, by default they still use whatever is the search_path at run-time, but this can be changed with an explicit SET associated to the function's declaration.

For example getLast() could be declared in the SQL script as:

CREATE OR REPLACE FUNCTION getLast()
RETURNS text AS $$
 SELECT my_col FROM my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE SET search_path TO :"SCH";

Answer 3

First of all, I'll say that if you can use @daniel-vérité's solution and set the search_path, do that. If you need to interpolate a variable for more than schema scoping, @dezso's answer works, but can be refined and simplified slightly.

A solution here can be found by realizing that the regular syntax for creating a function already uses a string as the body of the function.

CREATE FUNCTION :SCH.getLast() ... AS <somestring>;

So embedding a psql script variable in the function body boils down to figuring out how to embed that into a string. For readability, use $$ instead of single quotes, set the psql variable with gset, then interpolate it as a string with :'<varname>' (note the quotes).

SELECT $$
BEGIN
 SELECT my_col FROM $$ || :SCH || $.my_table ORDER by id DESC LIMIT 1;
END
$$ as funcbody
\gset
CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS :'funcbody'
LANGUAGE sql STABLE;