CREATE OR REPLACE FUNCTION _Foo2(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE
BEGIN
RETURN QUERY EXECUTE FORMAT('
SELECT %I, %I, %I
FROM %I
WHERE %I BETWEEN ''%s'' AND ''%s'';
',
'in_key', 'in_tst', 'in_val', 'in_table', 'in_tst', st, fin);
END;
$$ LANGUAGE plpgsql;Надо подсчитать количество строк в ответе запроса вида
SELECT COUNT(*) AS cnt
FROM (
SELECT 1
FROM "order" AS o
LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10
) xSELECT o.id, COUNT(o.id) OVER () AS cnt
FROM "order" AS o
LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10Я не знаю их архитектуру и название таблиц заранее
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY quantity DESC) rn
FROM tablename
)
SELECT *
FROM cte
WHERE rn = 1;select jsonb_value,
jsonb_set(jsonb_value, '{requisites,kpp}', '"777"', false)
FROM test;SELECT *
FROM user_friend uf1
WHERE [NOT] EXISTS ( SELECT NULL
FROM user_friend uf2
WHERE uf2.friend_user_id = uf1.user_id )AND friend_user_id > user_id. SELECT MIN(`min_price`) OVER () as `min_price`,
MAX(`max_price`) OVER () as `max_price`,
`item_id` as `id`
FROM `product_table`
WHERE `id` IN (SELECT тут запрос)
Akina, да, версия 5.7.21 и она, я так понимаю, не поддерживает оконные функции.
SELECT t2.min_price,
t2.max_price,
t1.item_id as id
FROM product_table t1
CROSS JOIN ( SELECT MIN(min_price) AS min_price,
MAX(max_price) AS max_price
FROM product_table ) t2
WHERE t1.id IN (SELECT тут запрос)SELECT CASE WHEN sender_id = 1
THEN receiver_id
ELSE sender_id
END AS buddy
FROM message
WHERE 1 IN (sender_id, receiver_id)
GROUP BY buddy
ORDER BY MAX(sent_at)SELECT user.username
FROM user
JOIN ( SELECT CASE WHEN sender_id = 1
THEN receiver_id
ELSE sender_id
END AS id,
MAX(sent_at) sent_at
FROM message
WHERE 1 IN (sender_id, receiver_id)
GROUP BY 1 ) ids USING (id)
ORDER BY ids.sent_atSELECT DISTINCT (regexp_split_to_array(commend, E'\\s+'))[:3] AS first_3_words
FROM orders
ORDER BY first_3_words[2];SELECT DISTINCT
key,
FIRST_VALUE(value1) OVER (PARTITION BY key
ORDER BY CASE WHEN value1 IS NULL
THEN 1
ELSE 0 END, id DESC) AS value1,
FIRST_VALUE(value2) OVER (PARTITION BY key
ORDER BY CASE WHEN value2 IS NULL
THEN 1
ELSE 0 END, id DESC) AS value2,
FIRST_VALUE(value3) OVER (PARTITION BY key
ORDER BY CASE WHEN value3 IS NULL
THEN 1
ELSE 0 END, id DESC) AS value3
FROM tablename
-- WHERE key IN ( {список значений} )переписать таблицу в нормальную форму