create table banners(banner_id serial primary key, value text);
create table keywords(keyword_id serial primary key, banner_id int references banners);
do $$
declare
_banner_id int;
i int;
begin
FOR i IN 1..10 LOOP
INSERT INTO banners (value) VALUES ('My banner ' || i) RETURNING banners.banner_id INTO _banner_id;
FOR i IN 1..100 LOOP
INSERT INTO keywords(banner_id) VALUES (_banner_id);
END LOOP;
END LOOP;
end;
$$;select row_to_json(j.*) from (
select 'string' as parentTableFirstProp, to_json(
(
select to_json(array_agg(r.*)) from (
select s as childTableFirstProp from (select unnest('{another string,yet another string}'::text[]) as s) _
)as r
)
) as parentToChildReference
) as jСреди очевидных проблем, с которым я столкнусь - необходимось поддержывать отдельное подключения к PostgreSQL для каждого пользователя
SELECT root_id, level, count(*) as count FROM matrix GROUP BY root_id, level
HAVING count(*) < power(_type.degree, level);func HTML(c *Context, name string, data map[string]interface{}) {
if data == nil {
data = make(map[string]interface{})
}
data["CurrentUser"] = c.Get("CurrentUser")
render.HTML(c, name, data)
}create table core_keywords(
keyword_id serial primary key,
keyword varchar(100)
);
create unique index u_idx_keyword on core_keywords(lower(keyword));
create index t_idx_keywords on core_keywords using gin (lower(keyword) gin_trgm_ops);
create table core_keywords_entry(
keyword_id int not null references core_keywords,
created_at timestamp with time zone not null default CURRENT_TIMESTAMP,
primary key (keyword_id, created_at)
);select
e.keyword_id,
count(*)
from core_keywords_entry as e
join core_keywords as k using(keyword_id)
where lower(k.keyword) like '%something%'
group by 1