Category Archives: talks

My talk: What is a database?

I’ve presented this talk a week ago as a part of “Braviant Talks”, where people from different departments of our company talk about what their department is doing. It intended to be as non-technical as possible, which I think was achieved, at least to some extent, and … I just like how it turned out:). Enjoy 🙂

Advertisements

Leave a comment

Filed under Companies, events, talks

How to build this nested JSON

This post is in response to multiple requests from the PG Conf attendees to provide a “schematic” example of how to build a record set and to convert it to a JSON object (basically to illustrate our “Connecting Galaxies” talk.

Actually, if you click on the link above and download the presentation, you will see an extra slide at the end, which provides the link to the code below… but we know that people do not like “clicking the links” 🙂

So – here is it one more time!

--- Building complex JSON objects from multiple tables --
--- Deriving aggregates json_agg and n_array_agg
--- wrapping an array (of rows) into JSON converted to text for JDBC ----transfer 

— Test aggregation
create table aa (a_id text, a_name text);
insert into aa values (‘1’, ‘Anna’),(‘2′,’Bob’);

—- Aggregation on second level —–
create table bb (b_id text, ab_id text, b_num text, b_ph text);
insert into bb values (‘101’, ‘1’, ‘101-101’, ‘(800)-123’);
insert into bb values (‘1012’, ‘1’, ‘1012102’, ‘(800)-1234’),
( ‘201’, ‘2’, ‘201-201’, ‘(800)-1345′);

— add one more embedded array on the second level —–
create table cc (c_id text, ac_id text, c_st text, c_more text);
insert into cc values
( ’11’, ‘1’ , ‘ stst’, ‘more-more’),
( ’12’, ‘1’ , ‘ swsw’, ‘more-less’);
insert into cc values
( ’21’, ‘2’ , ‘ stst-2’, ‘more-mo2222re’),
( ’22’, ‘2’ , ‘ sws222w’, ‘more-less22’);

—- add third level , dd will be under bb —-

create table dd (d_id text, bd_id text, d_value text);
insert into dd values
( ‘1101’ , ‘101’, ‘dv11’),
( ‘1102’ , ‘101’, ‘dv12’),
( ‘2101’ , ‘201’, ‘dv22’);

—- CTE might be helpful to avoid multiple processing of big top-level tables —-

create or replace
function array_agg_next (agg_sta anyarray, val anyelement, b boolean)
returns anyarray as
$$
declare
out_array alias for $0;
begin
if b then
if agg_sta is null then
out_array := array [val ];
else
out_array := agg_sta || val ;
end if;
else
out_array := agg_sta ;
end if;
return out_array;
END;
$$ LANGUAGE plpgsql;

drop
function array_agg_final (agg_sta anyarray);
create or replace
function array_agg_final (agg_sta anyarray)
returns anyarray as
$$
declare final_array alias for $0;
begin
final_array := agg_sta;
return final_array ;
END;
$$ LANGUAGE plpgsql;

—–======================================================
create or replace
function array_agg_next (agg_sta anyarray, val anyelement, b boolean)
returns anyarray as
$$
begin
if b then
if agg_sta is null
then agg_sta := array [val ];
else agg_sta := agg_sta || val;
end if;
end if;
return agg_sta;
END;
$$ LANGUAGE plpgsql;

drop aggregate n_array_agg (anyelement, boolean);
CREATE AGGREGATE n_array_agg (anyelement, boolean) (
sfunc= array_agg_next,
— FINALFUNC = array_agg_final,
STYPE = anyarray
);

drop type if exists jaa cascade;
create type jdd as (ddi_key text, dd_value text);
create type jcc as (cc_key text, cc_st text, more text);
Create type jbb as (bb_key text, num text, ph text, “DD” jdd[]);
create type jaa as (p_key text, p_name text, “BB” jbb[], “CC” jcc[]);

—- collect rows of a specific type into an array, depends on the output type —–
create or replace
function collect_items (s text)
returns jaa[]
LANGUAGE plpgsql as
$body$
declare
result jaa[];
begin
select array_agg(single_item) into result
from
(select
row (
a_id, a_name,
n_array_agg( row (b_id, b_num, b_ph, d_agg )::jbb , b_id is not null),
n_array_agg( row (c_id, c_st, c_more )::jcc, c_id is not null)
) ::jaa as single_item
from
(
select a_id, a_name, b_id, b_num, b_ph, c_id, c_st, c_more,
n_array_agg (row(d_id, d_value)::jdd, d_id is not null) as d_agg
from
(
select a_id, a_name, b_id, b_num, b_ph, null as c_id, null as c_st, null as c_more, d_id, d_value
from aa join bb on a_id = ab_id
left join dd on b_id = bd_id
UNION ALL
select a_id, a_name, null as b_id, null as b_num, null as b_ph, c_id, c_st, c_more, null as d_id, null as d_value
from aa join cc on a_id = ac_id
) temp_table_3
group by
a_id, a_name, b_id, b_num, b_ph, c_id, c_st, c_more
) temp_table_2
group by a_id, a_name) items
where 0=1
;
return result;
end;
$body$;

—– just another output type —-
create or replace
function collect_subitems (s text)
returns jbb[]
LANGUAGE plpgsql as
$body$
declare
result jbb[];
begin
select array_agg(single_item) into result
from
(
select row(
b_id, b_num, b_ph, n_array_agg (row(d_id, d_value)::jdd, d_id is not null)
)::jbb as single_item
from bb left join dd on b_id = bd_id
group by b_id, b_num, b_ph
) items;
return result;
end;
$body$;

— wrap array into a set of text strings containing the json representation of array elements
— This function does not depend on any type
create or replace
function array_transport (all_items anyarray) returns setof text
RETURNS NULL ON NULL INPUT
LANGUAGE plpgsql as
$body$
declare
item record;
begin
foreach item in array all_items
loop
return next( to_json(item))::text;
end loop;
end;
$body$;

select * from array_transport (collect_items(‘a’));

select * from array_transport (collect_subitems(‘a’));

===============================

 

Leave a comment

Filed under research, SQL, talks

PG Conf New York

It’s the first time I’ve attended the New York PG Conf, and we had two talks ln the very first day of Development track! I was a little bit nervous, but it went really well!

Tha presentation which we did together with Alyssa Ritchie was almost the same we did on PG Open and 2Q PG Conf (except that I’ve decided to change it significantly the night before!), and my short presentation was an extended presentation about remote function calls, which I did at the 2Q PG Conf. And here is it’s recording:

Leave a comment

Filed under events, talks

Radio broadcast about Chicago PUG

If you didn’t have a chance to listed to the live broadcast lat Friday – enojoy the recording:

https://www.mixcloud.com/widget/iframe/?light=1&feed=%2Flumpenradio%2Ftech-scene-chicago-host-melanie-adcock-01042019%2F

Leave a comment

Filed under events, People, talks

Braviant Holdings talks at 2Q PG Conf

Better later, than never: by popular demand here are the videos of both talks from Braviant Holdings, presented at 2Q  PG Conf. Enjoy 🙂


 

Leave a comment

Filed under Companies, events, talks

2Q PG Conf 2018 Chicago was a success!

It’s the second time that 2QPGConf was coming to Chicago, and this time around it was even better, than last year! This year it was a two-days conference, with a day of training preceding the actual conference day.

I’ve been asked by many people, what exactly did I like about the conference, so I thought I will put it in writing.

First, I really like an idea of regional conferences, I think they play a very important role in giving the users direct access to the leading specialists, an opportunity to know what other people are doing, and all of it at a reasonable cost. Not all the companies are as good as mine to have a budget for professional development for each employee, and many are reluctant to let their employees being away from work for a week. Or, as I was told, sometimes companies won’t even allow their employees to attend the conference unless they have an accepted talk. And it is so much easier to convince your manager to let you go for a day :).

Secondly, I really liked the talks, I think it was an excellent selection, both on technical level and on variety of topics and areas of use. These talks sparked a lot of conversations, and lots of new contacts have been made. Actually I think a couple of extra hours of networking would be great.

For me the most important thing was that I was able to promote Chicago PostgreSQL User group. I am really happy that our group is now considered to be one of the most successful in the country, and that people turn to me for advice on how to start (or re-energize) the groups in other cities. I hope that my work helps to promote Postgres as a database of choice. Actually in his opening keynote Tom Kincaid was talking about Postgres becoming more and more popular among developers, and I think that people are just starting to realise all it’s potential!

Leave a comment

Filed under events, talks

The 2Q PGCONF program just announced!

Hi Chicago! I am extremely excited to share with you the program of the upcoming 2Q PGCONF! After the last year’s success the organizers decided to add a whole day f training to the conference.

So, here is what you need to know: the training schedule is here. The talks schedule is here. Exciting news for my company – two talks from Braviant Holdings were accepted! I think it tells a lot about how our company foster innovation and invest in employees professional development.

And now the important dates to remember: the training is on Tue December 4, and the talks are on Wed December 5, and October 14 is the last day of the early bird registration!!!

And finally the last thing. The Chicago PostgreSQL User Group meetup will be next week, Wed Oct 17, featuring Shaun Thomas. Please come and have a sneak peek of the conference!

Leave a comment

Filed under Companies, events, news, talks