0

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

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.

0

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";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.