Variable substitution inside plpgsql functions

Question

To be able to create different instances of an application inside one database, like for example test staging, I would like to use variables for the schema name. Everything works when creating tables or sequences, but I can not figure out an elegant way to use variables inside functions. I have read already some articles about dollar-quoted string constants, but I still hope to find a solution.

Currently running PostgreSQL 11.

Here is the code:

Works as expeced, setting schema_name to sc and using the variable during DDL:

\set schema_name sc

CREATE SEQUENCE :schema_name.S_VT_KEY INCREMENT BY 1 START WITH 1 CACHE 1 NO CYCLE OWNED BY NONE;

create table :schema_name.vartest (
  VT_KEY   BIGINT,
  DESCRIPTION TEXT);

And now the function:

create or replace function :schema_name.fvartest(
  p_desc  TEXT)
  RETURNS bigint
  language 'plpgsql'
  COST 100.0
  VOLATILE NOT LEAKPROOF
AS $function$
DECLARE
  p_key bigint;
BEGIN

  select nextval('sc.S_VT_KEY') into p_key; -- (1)

  insert into sc.vartest (VT_KEY, DESCRIPTION) -- (2)
      values (p_key, p_desc);

  return p_key;

END $function$;

In case (1) I would like to use something like:

  select nextval(:schema_name || '.S_VT_KEY') into p_key;

In case (2)

  insert into :schema_name.vartest (VT_KEY, DESCRIPTION) -- (2)

Any variant so far renders an error message:

Using directly:

select nextval(:schema_name || '.S_VT_KEY') into p_key;

renders a syntax error

psql:test/testcase001.sql:29: ERROR:  syntax error at or near ":"
LINE 12:  select nextval(:schema_name || '.S_VT_KEY') into p_key; -- ...

Escaping, compiles but does not run:

select nextval($e$:schema_name$e$ || '.S_VT_KEY') into p_key; -- (1)

CONTEXT:  SQL statement "select nextval($e$:schema_name$e$ || '.S_VT_KEY')"

Almost there, using same delimiters, substitues the variable but does render a syntax error:

select nextval($function$ :'schema_name' $function$ || '.S_VT_KEY') into p_key; -- (1)

LINE 12:  select nextval($function$ 'sc' $function$ || '.S_VT_KEY')

Maybe there is a way in PostgreSQL 12?

Can you pass schema name in as an argument, so you don't have to use the :-prefixed session var? — richyen
Hi @richyen, passing the schema name as an argument is possible, but solves only half of the problem. You can create a string with the sequence name passing it to the nextval() function. It will not help with the prefix for table names (2) like in select or update. Additionally you need to pass the variable to every function which is redundant and not very elegant. — Peter Balon
I guess I would go for dynamic SQL. Have a look at the plpgsql command EXECUTE (which is not the same as the SQL command with the same name): postgresql.org/docs/current/… — Islingre
In combination with an argument that gets the :schema_name as default. So you will not need to access the variable inside the dollar-quoting — Islingre
"create different instances of an application inside one database" - I would recommend to create multiple databases (inside the same cluster) instead — Bergi

Peter Balon Peter Balon · Accepted Answer · 2019-11-11T06:47:47

I've come up with a solution which works for now, but is by far not that elegant. I'm using bash variable substitution and it seems to work for the two cases mentioned above:

#!/bin/bash

export schema_name="sc"

psql postgresql://<user>:<pwd>@<host>/<db>  << EOF


create or replace function ${schema_name}.fvartest(
  p_desc  TEXT)
  RETURNS bigint
  language 'plpgsql'
  COST 100.0
  VOLATILE NOT LEAKPROOF
AS \$function\$
DECLARE
    p_key bigint;
BEGIN

    select nextval('${schema_name}.S_VT_KEY') into p_key; -- (1)

    insert into ${schema_name}.vartest (VT_KEY, DESCRIPTION) -- (2)
        values (p_key, p_desc);

    return p_key;

END \$function\$;

    select ${schema_name}.fvartest('bla bla');
    select * from ${schema_name}.vartest;

EOF

But now I have to put the code into a bash script and escape the $function$ Postges style $$ notation. Maybe there is still a better way, would love to hear from you.

Variable substitution inside plpgsql functions

1 Answers