-
-
Save liquidgenius/20191d9f35cf830916e22c5973c4c505 to your computer and use it in GitHub Desktop.
How to use psql, plpgsql and session variables together in one file
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- psql variables can only be used outside functions | |
-- plpgsql variables can only be used inside functions | |
-- session variables can be used both outside and inside functions, bridging the gap between the two types of variables | |
\set func_name vars | |
\echo '[':func_name'.sql]' | |
CREATE OR REPLACE FUNCTION :func_name (arg_execute TEXT) | |
RETURNS VOID AS $$ DECLARE | |
BEGIN | |
END; | |
$$ LANGUAGE plpgsql; | |
SET SESSION skylab.func_string TO :'func_name'; -- transferring a psql variable into a session variable | |
SELECT * FROM plpgsql_check_function(current_setting('skylab.func_string')) AS errors; | |
DO $$ BEGIN | |
IF (SELECT COUNT(*) FROM plpgsql_check_function_tb(current_setting('skylab.func_string'))) > 0 THEN -- and then transferring the session variable into plpgsql | |
RAISE EXCEPTION '%', current_setting('skylab.func_string'); | |
END IF; | |
END $$; | |
SELECT :func_name('execute'); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment