Postgres: How to use variables in create functions constructions?

Question

I want to load functions from my shared library. To do this, I specify the path to the library after AS and I want to do it using a variable. But the value is not substituted in this way. How can this be done ?

    DO $$
    DECLARE
        pathToLib text := '/path/to/lib/myPostgresFunctionsLib.so';
    BEGIN
        CREATE OR REPLACE FUNCTION someFunction() RETURNS text
             AS pathToLib, 'someFunction'
             LANGUAGE C;
    END $$;

It returns:

    ERROR:  syntax error at or near "pathToLib"
    LINE 6:          AS pathToLib, 'someFunction'

Laurenz Albe · Accepted Answer · 2022-09-22 12:13:44Z

1

You need dynamic SQL:

DO
$$DECLARE
    pathToLib text := '/path/to/lib/myPostgresFunctionsLib.so';
BEGIN
    EXECUTE format(
                $_$CREATE OR REPLACE FUNCTION someFunction() RETURNS text
                   AS %L, 'someFunction'
                   LANGUAGE C$_$,
                pathToLib
            );
END;$$;

format() will replace %L with the value of pathToLib, correctly escaped as a string literal.

answered Sep 22, 2022 at 12:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: How to use variables in create functions constructions?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related