Re: proposal: session server side variables
| От | Pavel Stehule |
|---|---|
| Тема | Re: proposal: session server side variables |
| Дата | |
| Msg-id | CAFj8pRBP89h+KPzGL_6B55NURTNCFORr0Oqj+xXsMitZzVYQWQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: proposal: session server side variables (Artur Zakirov <a.zakirov@postgrespro.ru>) |
| Список | pgsql-hackers |
Hi
2016-11-28 10:39 GMT+01:00 Artur Zakirov <a.zakirov@postgrespro.ru>:
On 28.11.2016 10:42, Pavel Stehule wrote:
next update - setattr, getattr functions are working now
notes, comments?
Regards
Pavel
It is interesting!
Do you have plans to support also table variables? For example, like this:
create type composite_type_2 as (a int, b text);
create variable var7 composite_type_2;
select insertvar('var7','(10,Hello world\, Hello world\, Hello world)');
select insertvar('var7','(1000,Hola, hola!)');
select * from getvar('var7');
a | b
------+---------------------------------------
10 | Hello world, Hello world, Hello world
1000 | Hola, hola!
Or it is a bad idea? Or it is not related to this patch?
Minimally in first stage I have not plan to support tables. It opens lot of questions - lot of code to implement - how to implement indexes, statistics, MVCC?
But some workaround is not hard - you can store a array of composite types.
postgres=# select setvar('a', array(select row(10,'ahoj')::test from generate_series(1,10)));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ a
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
│ {"(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,aho
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
(1 row)
Time: 0,992 ms
postgres=# select * from unnest(getvar('a'));
┌────┬──────┐
│ a │ b │
╞════╪══════╡
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
└────┴──────┘
(10 rows)
postgres=# select setvar('a', array(select row(10,'ahoj')::test from generate_series(1,10)));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ a
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
│ {"(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,ahoj)","(10,aho
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
(1 row)
Time: 0,992 ms
postgres=# select * from unnest(getvar('a'));
┌────┬──────┐
│ a │ b │
╞════╪══════╡
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
│ 10 │ ahoj │
└────┴──────┘
(10 rows)
For fast append it needs another significant work (and can be done in next step), but almost all work did Tom already.
We have the extension (https://github.com/postgrespro/pg_variables). And it supports table like variables. It shows better performance against temporary tables.
--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
В списке pgsql-hackers по дате отправления: