Skip to content

Instantly share code, notes, and snippets.

@liquidgenius
Forked from bokwoon95/vars.sql
Created August 8, 2020 15:11
Show Gist options
  • Save liquidgenius/20191d9f35cf830916e22c5973c4c505 to your computer and use it in GitHub Desktop.
Save liquidgenius/20191d9f35cf830916e22c5973c4c505 to your computer and use it in GitHub Desktop.
How to use psql, plpgsql and session variables together in one file
-- 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