Postgresql – Use psql –set variable in a function

postgresqlpostgresql-9.5psqlscripting

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');

Best Answer

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.

Related Question