Re: [HACKERS] proposal: schema PL session variables

2016-10-11 Thread Pavel Stehule
Hi 2016-02-23 20:52 GMT+01:00 Pavel Stehule : > > > 2016-02-12 22:41 GMT+01:00 Jim Nasby : > >> On 2/12/16 2:58 PM, Pavel Stehule wrote: >> >>> >>> So I think adding something like this needs to at least address >>> *how* SQL level

Re: [HACKERS] proposal: schema PL session variables

2016-02-23 Thread Pavel Stehule
2016-02-12 22:41 GMT+01:00 Jim Nasby : > On 2/12/16 2:58 PM, Pavel Stehule wrote: > >> >> So I think adding something like this needs to at least address >> *how* SQL level access would work, *when* it's eventually implemented. >> >> >> I understand - and I

Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Pavel Stehule
2016-02-12 22:41 GMT+01:00 Jim Nasby : > On 2/12/16 2:58 PM, Pavel Stehule wrote: > >> I think that's probably true, but this also shows why we need to consider >> different PLs too. As it stands right now, the only way to access a >> variable outside of plpgsql would be

Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Jim Nasby
On 2/10/16 1:29 PM, Pavel Stehule wrote: I got off list mail with little bit different syntax proposal CREATE VARIABLE xxx DEFAULT [ PRIVATE ] I am thinking so more SQL natural is form: CREATE [ PRIVATE ] VARIABLE xxx ... There should not be only variables, there can be tables, views,

Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Jim Nasby
On 2/12/16 2:58 PM, Pavel Stehule wrote: So I think adding something like this needs to at least address *how* SQL level access would work, *when* it's eventually implemented. I understand - and I agree. small note: Private variables should not be executed from any SQL, because SQL

Re: [HACKERS] proposal: schema PL session variables

2016-02-12 Thread Pavel Stehule
Hi >> SQL access to variables needs a) change in SQL parser (with difficult >> discussion about syntax) or b) generic get/set functions. @b can be used >> in other PL in first iteration. >> >> I afraid to open pandora box and I would to hold the scope of this patch >> too small what is possible

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-09 23:31 GMT+01:00 Jim Nasby : > On 2/8/16 10:02 AM, Pavel Stehule wrote: > >> >> I think it would make sense to implement the interface in at least >> one of our other supported PLs. I'm not entirely clear how well this >> will match up with, say,

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
Hi Would it make sense to explicitly import variables in function definitions? > > CREATE SESSION VARIABLE foo integer; > CREATE SESSION VARIABLE my_schema.bar text; > SET SESSION VARIABLE foo to 4; > SET SESSION VARIABLE my_schema.bar to 'hi mom'; > > CREATE FUNCTION my_func (p_param text)

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-09 20:55 GMT+01:00 David G. Johnston : > On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker > wrote: > >> >> Oh, and I suggest we call them SESSION variables rather than SCHEMA >> variables, to reinforce the idea of how long the values in

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby
On 2/10/16 11:25 AM, Pavel Stehule wrote: Oh, and I suggest we call them SESSION variables rather than SCHEMA variables, to reinforce the idea of how long the values in the variables live. A session variable is in a sense a 1x1 temp table, whose definition persists across

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
>> I didn't propose SESSION variables - now there are some workarounds how >> to anybody can emulate it, so this feature can wait. What we need is >> safe session variables with limited access. And the border can be >> defined by schema scope. So the keyword SCHEMA has sense, and it is >>

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-10 20:25 GMT+01:00 Jim Nasby : > On 2/10/16 1:17 PM, Pavel Stehule wrote:It is too simple and too like > workaround :) I can do it this in plpgsql > >> extension probably. >> > > I think it's something people will definitely want. If we don't have it, > then

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby
On 2/10/16 1:04 PM, Pavel Stehule wrote: BTW, if all that's desired here are session variables for plpgsql, I think it makes a lot more sense to start with implementing per-function session variables. That's a lot simpler design-wise and is something we should have anyway. You

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby
On 2/10/16 11:33 AM, Pavel Stehule wrote: I don't think that really makes sense. I would expect schema variables to be exposed to a function as variables or attributes, either in the global namespace for that PL, or as an attribute of some object (ie the plpy object in

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
2016-02-10 20:10 GMT+01:00 Jim Nasby : > On 2/10/16 1:04 PM, Pavel Stehule wrote: > >> >> BTW, if all that's desired here are session variables for plpgsql, I >> think it makes a lot more sense to start with implementing >> per-function session variables.

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby
On 2/10/16 11:54 AM, Pavel Stehule wrote: 2016-02-09 23:41 GMT+01:00 Jim Nasby >: The other big thing you get is public vs private. You can sorta-kinda-almost simulate that with permissions in simple cases, but it ultimately

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Jim Nasby
On 2/10/16 1:17 PM, Pavel Stehule wrote: 2016-02-10 20:10 GMT+01:00 Jim Nasby >: On 2/10/16 1:04 PM, Pavel Stehule wrote: BTW, if all that's desired here are session variables for plpgsql, I

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
>> >> The schema variables are private by design. It can be enhanced in >> future, but now it is out my scope. If you need public access to these >> variables, you can use a functions. The access to functions can be >> controlled by a rights. We can introduce a private (schema limited) >> function

Re: [HACKERS] proposal: schema PL session variables

2016-02-10 Thread Pavel Stehule
Hi 2016-02-09 23:41 GMT+01:00 Jim Nasby : > On 2/9/16 4:13 PM, Corey Huinker wrote: > >> >> We're not going to get source compatibility without implementing >> packages, and there's no enthusiasm for that. It's been stated a few >> times before by some that the only

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Marko Tiikkaja
On 08/02/16 14:16, Pavel Stehule wrote: 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja : Yeah, and that's exactly what I don't want, because that means that CREATE SCHEMA VARIABLE suddenly breaks existing code. theoretically yes, but this conflict can be 100% detected - so no quiet

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Pavel Stehule
2016-02-09 15:32 GMT+01:00 Marko Tiikkaja : > On 08/02/16 14:16, Pavel Stehule wrote: > >> 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja : >> >>> >>> Yeah, and that's exactly what I don't want, because that means that >>> CREATE >>> SCHEMA VARIABLE suddenly breaks existing

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Jim Nasby
On 2/9/16 4:13 PM, Corey Huinker wrote: We're not going to get source compatibility without implementing packages, and there's no enthusiasm for that. It's been stated a few times before by some that the only value they see in packages is the package/session variables. Pavel's idea gives us

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Corey Huinker
On Tue, Feb 9, 2016 at 2:55 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker > wrote: > >> >> Oh, and I suggest we call them SESSION variables rather than SCHEMA >> variables, to reinforce the idea of how long

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Jim Nasby
On 2/8/16 10:02 AM, Pavel Stehule wrote: I think it would make sense to implement the interface in at least one of our other supported PLs. I'm not entirely clear how well this will match up with, say, plperl, but I'd be interested to see. The minimalistic interface can be based

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread David G. Johnston
On Tue, Feb 9, 2016 at 11:32 AM, Corey Huinker wrote: > > Oh, and I suggest we call them SESSION variables rather than SCHEMA > variables, to reinforce the idea of how long the values in the variables > live. A session variable is in a sense a 1x1 temp table, whose

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Corey Huinker
On Tue, Feb 9, 2016 at 9:58 AM, Pavel Stehule wrote: > > > 2016-02-09 15:32 GMT+01:00 Marko Tiikkaja : > >> On 08/02/16 14:16, Pavel Stehule wrote: >> >>> 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja : >>> Yeah, and that's exactly what

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 16:45 GMT+01:00 jflack : > On 02/08/2016 03:16 AM, Pavel Stehule wrote: > > > Only a owner of schema can edit functions inside schema > > Can't anyone granted CREATE on the schema do that? Would > that be changed by this proposal? > yes, anybody with necessary

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
Hi >> I propose really basic functionality, that can be enhanced in future - >> step by step. This proposal doesn't contain any controversial feature or >> syntax, I hope. It is related to PLpgSQL only, but described feature can be >> used from any PL languages with implemented interface. >> > >

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Chapman Flack
[resending because thunderbird helpfully defaulted my sender address to the one that -isn't- subscribed to -hackers, sorry] On 02/08/2016 03:16 AM, Pavel Stehule wrote: > Only a owner of schema can edit functions inside schema Can't anyone granted CREATE on the schema do that? Would that be

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja
On 08/02/16 09:16, Pavel Stehule wrote: Usage = DROP SCHEMA IF EXISTS test_schema CASCADE; SET SCHEMA test_schema; CREATE SCHEMA VARIABLE local_counter AS int DEFAULT 0; CREATE OR REPLACE FUNCTION increment_counter() RETURNS void AS $$ BEGIN local_counter := local_counter + 1; END; $$

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja
On 08/02/16 13:17, Pavel Stehule wrote: 2016-02-08 13:03 GMT+01:00 Marko Tiikkaja : How does this function know which schema variables are visible? function see all schema variables from same schema as function's schema Personally I find that undesirable. I don't know what

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja
On 08/02/16 13:41, Pavel Stehule wrote: 2016-02-08 13:22 GMT+01:00 Marko Tiikkaja : Personally I find that undesirable. I don't know what oracle does, but variables being visible without schema-qualifying them can introduce variable conflicts in PL/PgSQL. I'd prefer if you could

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 13:03 GMT+01:00 Marko Tiikkaja : > On 08/02/16 09:16, Pavel Stehule wrote: > >> Usage >> = >> >> DROP SCHEMA IF EXISTS test_schema CASCADE; >> SET SCHEMA test_schema; >> >> CREATE SCHEMA VARIABLE local_counter AS int DEFAULT 0; >> >> CREATE OR REPLACE FUNCTION

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 13:22 GMT+01:00 Marko Tiikkaja : > On 08/02/16 13:17, Pavel Stehule wrote: > >> 2016-02-08 13:03 GMT+01:00 Marko Tiikkaja : >> >>> How does this function know which schema variables are visible? >>> >> >> function see all schema variables from same schema as

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Andrew Dunstan
On 02/08/2016 03:16 AM, Pavel Stehule wrote: Hi On Russian PgConf I had a talk with Oleg about missing features in PLpgSQL, that can complicates a migrations from Oracle to PostgreSQL. Currently I see only one blocker - missing protected session variables. PL/SQL has package variables with

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Pavel Stehule
2016-02-08 13:53 GMT+01:00 Marko Tiikkaja : > On 08/02/16 13:41, Pavel Stehule wrote: > >> 2016-02-08 13:22 GMT+01:00 Marko Tiikkaja : >> >>> Personally I find that undesirable. I don't know what oracle does, but >>> variables being visible without schema-qualifying