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.


At June 26, 2012 at 7:21 AM , Blogger Bob Henkel said...

So what is the benefit of this? Honest question, curios how you intend to use this.

At June 26, 2012 at 7:21 AM , Blogger Bob Henkel said...

This comment has been removed by the author.

At June 26, 2012 at 8:23 AM , Anonymous Igor Neyman said...

So, this will be in 9.3?

At June 27, 2012 at 4:45 AM , Blogger Pavel Stěhule said...

to Bob

A expected benefit is simplification of implementation of simple applications based on psql. Other possible benefit is emulation of 3G databases - you can do request to client for data inside stored procedure execution - similar functionality is SQL+

At June 27, 2012 at 4:46 AM , Blogger Pavel Stěhule said...

To Igor: No this is just proof concept and it will not be (probably in any future version in postgres), but this idea was not rejected


Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home