How to access outer scope variables from a function in PostgreSQL?

Question

I have this code:

DO $$
DECLARE
    NODE_ID bigint :=  46;
BEGIN
    CREATE OR REPLACE FUNCTION funk(VAL bigint) 
    RETURNS bigint AS $f$
        BEGIN
            RETURN VAL;
        END; $f$ LANGUAGE plpgsql;

    RAISE NOTICE '%', funk(NODE_ID);
END $$;

I works as expected and prints 46 to the console. I want to get rid of the parameters, because the variable is global. But I am getting errors:

DO $$
DECLARE
    NODE_ID bigint :=  46;
BEGIN
    CREATE OR REPLACE FUNCTION funk() 
    RETURNS bigint AS $f$
        BEGIN
            RETURN NODE_ID;
        END; $f$ LANGUAGE plpgsql;

    RAISE NOTICE '%', funk();
END $$;

I'm getting "NODE_ID not exist". Is there a way to access the outer variable in the function?

Laurenz Albe · Accepted Answer · 2019-01-31 10:45:42Z

1

No, that won't work, because the function has no connection to your DO block whatsoever. It is a persistent database object that will continue to exist in the database after the DO block has finished.

In essence, a function is just a string with the function body (and some metadata, see pg_proc); in this case, the function body consists of the text between the opening and the closing $f$ . It is interpreted by the language handler when the function is run.

The only database data you can reference in a function are other persistent database objects, and a variable in a DO block isn't one of those.

There are no global variables in PostgreSQL except for – in a way – the configuration parameters. You can access these with the SET and SHOW SQL commands and, more conveniently in code, with the set_config and current_setting functions.

answered Jan 31, 2019 at 10:45

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

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

Comments

Tomáš Záluský · Accepted Answer · 2019-10-14 13:46:49Z

0

Or use dynamic SQL:

DO $$
DECLARE
    NODE_ID bigint :=  46;
    src text := format('
        CREATE OR REPLACE FUNCTION funk() 
        RETURNS bigint AS $f$
            BEGIN
                RETURN %s;
            END; 
        $f$ LANGUAGE plpgsql;
    ', NODE_ID::text);
BEGIN
    execute src;
    RAISE NOTICE '%', funk();
END $$;

(works for me, landing on your question searching for solution of same problem)

answered Oct 14, 2019 at 13:46

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

Collectives™ on Stack Overflow

How to access outer scope variables from a function in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related