Postgres: How to use variables in create functions constructions?-CodePudding

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'

CodePudding user response：

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.