Specjalistą nie jestem od baz danychy, ale bym zrobił tak:
\timing off
\pset pager off
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
\c postgres;
DROP DATABASE IF EXISTS test2;
DROP ROLE IF EXISTS test2;
CREATE ROLE test2 PASSWORD 'test2' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
CREATE DATABASE test2 OWNER test2 ENCODING 'UTF-8';
\c test2;
set role test2;
CREATE TABLE countries (
id serial NOT NULL,
name character varying(10) NOT NULL
);
CREATE TABLE users (
id serial NOT NULL,
id_country integer NOT NULL,
name character varying(10) NOT NULL,
price double precision
);
create or replace function random_string(length integer) returns text as
$$
declare
chars text[] := '{A,B,C,D,E,F,G,H}';
result text := '';
i integer := 0;
begin
for i in 1..length loop
result := result || chars[1+random()*(array_length(chars, 1)-1)];
end loop;
return result;
end;
$$ language plpgsql;
create or replace function mk_countries(length integer) returns void as
$$
declare
i integer;
begin
for i in 1..length loop
execute 'insert into countries (name) values( random_string(5) )' ;
end loop;
end;
$$ language plpgsql;
create or replace function mk_users(length integer) returns void as
$$
declare
i integer;
p double precision;
cnt_country integer;
id_country integer;
begin
select count(*) into cnt_country from countries;
for i in 1..length loop
id_country = floor( random() * cnt_country ); -- zawsze przynajmniej jeden kraj ma zero userów, więc będzie zero w testach
p = random() * 20000 + 3000;
execute 'insert into users (id_country,name,price) values( $1 , random_string(10) , $2 )' using id_country, p;
end loop;
end;
$$ language plpgsql;
select mk_countries(30);
select mk_users(10000);
select
c.name as NAZWA,
(select count(*) from users as u1 where u1.id_country = c.id) AS LICZBA,
(select avg(u2.price) from users as u2 where u2.id_country = c.id) AS PLACA
from
countries as c
order by
c.name;