Monday, June 25, 2012

enabling access from stored procedure to host variables

I am trying to join two different worlds - stored procedures (server side) and host environment (client side). Why? We have a good server side language - plpgsql, and we have good script environment - psql console. Console has own environment, but it is not accessible from plpgsql. I wrote two functions, that enables it:
postgres /home/pavel $ psql postgres -v myvar=hello
psql (9.3devel)
Type "help" for help.

postgres=# \echo :myvar
postgres=# do $$begin raise notice 'myvar=>>%<<', get_hostvar_text('myvar'); end; $$ language plpgsql;
NOTICE:  myvar=>>hello<<

postgres=# do $$begin perform set_hostvar_text('myvar', 'hello world'); end $$ language plpgsql;
postgres=# \echo :myvar
hello world
This is just concept without any optimization. It can be cached on server side, it can be better integrated to language.