I sent new patch to mailing list - can be composite, scalar or array - and it is working like plpgsql variables (but are placed to schema, and content is session limited) - that was my target:
postgres=# create variable foo as numeric default 0;
CREATE VARIABLE
postgres=# select foo;
┌─────┐
│ foo │
╞═════╡
│ 0 │
└─────┘
(1 row)
postgres=# let foo = pi();
LET
postgres=# select foo;
┌──────────────────┐
│ foo │
╞══════════════════╡
│ 3.14159265358979 │
└──────────────────┘
(1 row)
postgres=# create variable boo as (x numeric default 0, y numeric default 0);
CREATE VARIABLE
postgres=# let boo.x = 100;
LET
postgres=# select boo;
┌─────────┐
│ boo │
╞═════════╡
│ (100,0) │
└─────────┘
(1 row)
postgres=# select boo.x;
┌─────┐
│ x │
╞═════╡
│ 100 │
└─────┘
(1 row)
In new session, the variables are available - but the content was initialized:
[pavel@nemesis src]$ psql
psql (11devel)
Type "help" for help.
postgres=# \dV
List of relations
┌────────┬──────┬─────────────────┬───────┐
│ Schema │ Name │ Type │ Owner │
╞════════╪══════╪═════════════════╪═══════╡
│ public │ boo │ schema variable │ pavel │
│ public │ foo │ schema variable │ pavel │
└────────┴──────┴─────────────────┴───────┘
(2 rows)
postgres=# select boo;
┌───────┐
│ boo │
╞═══════╡
│ (0,0) │
└───────┘
(1 row)
These variables can be used like package variables in PL/SQL:
postgres=# create schema mypackage;
CREATE SCHEMA
postgres=# create variable mypackage.state as (name text, last_access timestamp, debug boolean default false);
CREATE VARIABLE
postgres=# let mypackage.state.name = 'Pavel';
LET
postgres=# let mypackage.state.last_access = current_timestamp;
LET
postgres=# select mypackage.state;
┌───────────────────────────────────────┐
│ state │
╞═══════════════════════════════════════╡
│ (Pavel,"2018-03-20 19:12:18.29888",f) │
└───────────────────────────────────────┘
(1 row)
postgres=# select mypackage.state.name;
┌───────┐
│ name │
╞═══════╡
│ Pavel │
└───────┘
(1 row)
How fast is it? Is it comparable with GUCs?
ReplyDeleteAlso, if I'd do:
select myvariable.*
will it have the same performance problem as doing things like:
select (some_function()).*
or will it behave more sanely?
@depesz Should be faster than GUC - data are stored in binary form, not in text like GUC
ReplyDeleteboo.* is not allowed is this moment -(boo).* is working as expected. It works exactly like functions - there is same transformation, but the overhead of variable reading is +/- zero (and can be reduced, if it will be requested). But in this case, the variable evaluation is just copy of some binary value - nothing is evaluated.
I am looking to code, and the expression x.* is directly interpreted like RTE.star - so it is out of my scope. The expression ().* is evaluated out of my scope too. So - variables has same behave like function, but against function, the cost of evaluation should be +/- 0 in normal cases. If somebody do some optimization with (fx()).*, then he fix variables too.
Looks very promising and it might help with mitrationm from Oracle. Thanks for the proposal!
ReplyDeleteWhat are think about constant variables?
ReplyDelete@Wojcieach Kolo - I have not a case for this - but the implementation is trivial - current implementation supports it is partially. For any user without UPDATE right the variable is constant.
ReplyDelete