CREATE FUNCTION fn_test()
RETURNS TRIGGER
AS $fn_test$
BEGIN
IF OLD.val1 <> NEW.val1 THEN -- задать условие
NEW.id := NEW.id * 10; -- выполнить действие
END IF;
RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION fn_test();CREATE FUNCTION fn_test()
RETURNS TRIGGER
AS $fn_test$
BEGIN
NEW.id := NEW.id * 10; -- выполнить действие
RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
WHEN (OLD.val1 <> NEW.val1) -- задать условие
EXECUTE FUNCTION fn_test();CREATE TABLE trs (
wallet_id INT,
amount DECIMAL(12, 2),
hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id + amount) :: TEXT :: BYTEA)) STORED
)из конкатенированный строки wallet_id + hash
CREATE TABLE trs (
wallet_id INT,
amount DECIMAL(12, 2),
hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id :: TEXT || amount :: TEXT) :: BYTEA)) STORED
)select *
from products
order by type ASC,
case WHEN type = 'books' then id ELSE 0 END desc,
case WHEN type = "food" then name ELSE 0 END asc,
case WHEN type = "food" then price ELSE 0 END desc,
case WHEN type = "relax" THEN popular ELSE 0 END asc,
case WHEN type = "relax" THEN id ELSE 0 END descCREATE TABLE tasks (
id PRIMARY KEY,
definition,
performer_id REFERENCES performer (id)
expired_at DATETIME
);UPDATE tasks
SET performer_id = @performer_idб
expired_at = NOW() + INTERVAL 'performing time'
WHERE ( expired_at IS NULL or expired_at < NOW() )
AND ( id = @task_id )n_flag может быть 0..5
num_status 0..20
...
запись, где n_flag=0 и num_status>=10.
WITH cte AS (
SELECT 'Bob' AS name, 25 AS age UNION ALL
SELECT 'Mark' , 30 UNION ALL
SELECT 'Joe' , 35
)
SELECT test.id,
jsonb_build_object('items', jsonb_agg(jae.value_1 || jsonb_build_object('age', cte.age)))
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'
GROUP BY test.idWITH cte AS (
SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
FROM product_in_purchase AS PIP
RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
GROUP BY PIP.purchase_id
)
( SELECT * FROM cte ORDER BY all_sum ASC LIMIT 1 )
UNION
( SELECT * FROM cte ORDER BY all_sum DESC LIMIT 1 )from
table1 , ( table2
left join table1 h on table1.id = h.id
left join table2 s on table2.id = s.id )FROM table1
CROSS JOIN table2
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.idFROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.idCREATE 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) >= 10select jsonb_value,
jsonb_value::text ~ '^{"en": "[^"]+", "ru": "[^"]+"}$'
from test