with vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max аналогично*/ from vertex group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge group by fid_id
)
select id, name, vertex_cost, edge_cost
from fig
left join vertex_cost vc on fig.id=vc.fig_id
left join edge_cost ec on fig.id=ec.fig_idwith figures as (
select id, name from figures where name like 'foo%'
),
vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max*/ from vertex where fig_id in (select id from figures) group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge where fig_id in (select id from figures) group by fid_id
)
select id, name, vertex_cost, edge_cost
from figures
left join vertex_cost vc on figures.id=vc.fig_id
left join edge_cost ec on figures.id=ec.fig_idAll constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.
return query select ....CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
RETURNS TABLE(
id bigint
)
AS
$BODY$
BEGIN
RETURN QUERY SELECT p.id FROM public.passengers p;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
RETURNS setof bigint
AS
$BODY$
BEGIN
RETURN QUERY SELECT id FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
RETURNS setof public.passengers
AS
$BODY$
BEGIN
RETURN QUERY SELECT * FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;pg_read_file Return the contents of a text file.
select
user_id,
date,
SUM(amount) filter(where currency = 'USD') AS USD,
SUM(amount) filter(where currency = 'USD') AS EUR
FROM Table1
GROUP BY user_id, datewith table2_insert as (
INSERT INTO table2 (column1) VALUES ('test') RETURNING id
)
INSERT INTO table1 (column1) select id from table2_insertCREATE UNIQUE INDEX test_good_default_goods ON test_good (good_id)
WHERE by_default = trueupdate test_good set by_default = true where id = :idupdate test_good set by_default = (id = :new_default_id) where good_id = :good_id