主页 / user-99705

eComEvo's questions

Martin Hope
eComEvo
Asked: 2019-12-20 12:50:48 +0800 CST
  • 0

我试图阻止任何人删除特定模式“公共”上的表，除非用户是一个rds_superuser但我编写的函数正在保护所有模式。

CREATE OR REPLACE FUNCTION guard_tables()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_TABLE_SCHEMA = 'public' AND (SELECT COUNT(*) FROM pg_roles WHERE pg_has_role(CURRENT_USER, oid, 'member') AND rolname = 'rds_superuser') = 0 THEN
RAISE EXCEPTION 'command % is disabled for this table', tg_tag;
END IF;
END;
$$;

我怎样才能让它工作？

postgresql plpgsql
Martin Hope
eComEvo
Asked: 2018-01-24 19:09:26 +0800 CST
  • 9

我正在尝试构建一个触发器功能，以在任何项目发生变化时自动更新父订单值。

这是我创建的：

CREATE OR REPLACE FUNCTION update_totals() RETURNS void AS $$
    UPDATE orders SET
       total_fees = (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id),
       total_profit = (CASE WHEN total_cost IS NOT NULL THEN total - total_tax - total_cost - (SELECT SUM(fees) FROM order_items WHERE order_id = OLD.order_id) ELSE NULL END)
    WHERE id = OLD.order_id;
$$
LANGUAGE SQL;

CREATE TRIGGER update_totals AFTER INSERT 
OR UPDATE OF fees
ON order_items INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

当我尝试运行它时，我收到此错误：

missing FROM-clause for table "old"

我尝试在触发器中创建对原始表的引用：

CREATE TRIGGER update_totals AFTER INSERT 
OR UPDATE OF fees
ON order_items
REFERENCING OLD ROW AS old_order
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN ( OLD.* IS DISTINCT FROM NEW.* ) EXECUTE PROCEDURE update_totals();

但这只是给出了错误syntax error at or near "REFERENCING"。

如何order_id从原始order_items表中获取？

postgresql functions
Martin Hope
eComEvo
Asked: 2018-01-12 11:24:39 +0800 CST
  • 1

我正在尝试使用此查询显示 12 个月的结果：

SELECT 
    COUNT(DISTINCT user_id) as customers,
    SUM(amount)*.01 as amount,
    SUM(amount_refunded)*.01 as refunded,
    MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH
AND `user_id` IN (SELECT `id` FROM `users` WHERE `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH);

可以使用 PHP 以编程方式轻松完成此操作，以输出到 HTML 表中，其中每行包含通过递增月份并每次再次查询数据库来显示不同的月份。

我将如何使用 MySQL FOR 循环来使用这个查询呢？这甚至是正确的方法吗？

mysql
Martin Hope
eComEvo
Asked: 2017-05-11 05:20:36 +0800 CST
  • 2

我有一张orders表，需要计算平均计数随时间变化的时间。这可能是增加也可能是减少。

比如平时两天1单，我需要检测是突然增加到每天3单，还是突然减少到每3天1单。

示例表结构和数据：

CREATE TABLE "public"."orders" (
    "id" SERIAL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');

在上面的数据集中，假设我想检测在过去一周内通常每 2 天只有 1 个订单，但在过去 24 小时内一天内有 3 个订单违反了该模式。

期望的结果：

  • 前 7 天的平均每天订单数（不包括最近 24 小时）：0.43
  • 前 7 天的平均订单速度（不包括最近 24 小时）：每 2 天 1 个订单
  • 过去 24 小时的订单数：3

替代方案：

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '7 days'),
(now() - interval '7 days'),
(now() - interval '7 days');

或者，假设我有上述数据集。通常过去一周每天大约有 3 个订单，但在过去 24 小时内只有 1 个订单。

期望的结果：

  • 前 7 天的平均订单数（不包括最近 24 小时）：2.57
  • 前 7 天的平均订单速度（不包括最近 24 小时）：每 1 天 3 个订单
  • 最近 24 小时的订单：1

我该怎么做？

PostgreSQL 9.6

postgresql postgresql-9.6
Martin Hope
eComEvo
Asked: 2017-05-05 10:07:33 +0800 CST
  • 2

我将如何查询 JSON 数组列，例如每个数组值中的文本，并且只返回那些包含它们的行？

["Mr Smith","Ms Wellington","Mr Anderson"]

我已经尝试过这些，但是当我知道它们存在时都没有返回结果：

select * from people where names::jsonb ? 'Mr';
select * from people where names::jsonb @> 'Mr';
postgresql postgresql-9.6
Martin Hope
eComEvo
Asked: 2017-03-29 17:50:50 +0800 CST
  • 4

我有这个people和tags桌子，像这样，

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

如果我想使用连接在表中查找同时包含 和 标签的所有内容people，我将如何在 Postgres 9.6 中高效地执行此操作？npywtags

people在这种情况下，我应该只从表中获取 Joe 的记录。

postgresql postgresql-9.6
Martin Hope
eComEvo
Asked: 2017-01-26 19:15:18 +0800 CST
  • 2

我正在尝试优化表的结构。为此，我已将列以最佳顺序排列，然后运行此查询（已简化）：

INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;

然后我简单地重命名表并添加所有相同的索引和外键。

ALTER TABLE "table_current" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table_current";
ALTER TABLE "table_current" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

这在过去非常有效，但在这种特殊情况下，还有很多其他表在该表上具有级联外键。

因此，如果我重新创建并重命名，那么所有其他表仍会引用table_old它们的外键而不是新的table_current.

我如何进行这样的表迁移而不必在每个其他表上重新创建外键？

postgresql postgresql-9.6
Martin Hope
eComEvo
Asked: 2016-09-28 13:52:26 +0800 CST
  • 3

我需要连接到我们的 AWS RDS PostgreSQL 9.5 实例，这样我就可以将citext扩展添加到pg_catalog架构中，以便它可用于所有架构（PG默认情况下会添加pg_catalog到开头search_path）。

我很容易在我的本地机器上做到这一点：

> sudo su - postgres
> psql
> \c mydatabase
> CREATE EXTENSION IF NOT EXISTS citext SCHEMA pg_catalog;
> \q
> exit

如果我使用 AWS 文档显示的直接连接登录到 RDS，permission denied for schema pg_catalog当我尝试CREATE EXTENSION：

psql
   --host=myrdsawshostname
   --port=5432
   --username=myusername
   --password
   --dbname=mydatabase

这是我知道如何连接的唯一方法，而且我似乎只能以“myusername”而不是“postgres”连接。

我在这里做错了什么？

postgresql permissions
Martin Hope
eComEvo
Asked: 2016-08-20 09:43:49 +0800 CST
  • 2

我在我的公共模式中创建了许多表。

有时，我需要在users执行测试时运行播种器来刷新表。为此，我正在执行TRUNCATE TABLE users RESTART IDENTITY CASCADE.

但是，除了与该表相关的表之外，我不希望任何其他表users受到影响。引用该表的唯一外键位于通过引用的users表上。该表按预期截断。teamsusersowner_idteams

出乎意料的是，该attributes表也被截断了TRUNCATE TABLE users RESTART IDENTITY CASCADE。

该表在引用该表的attributes表上有一个外键。但是，在表中，所有值都是如此，因此没有对表中特定记录的任何记录的实际引用。实际上，在执行truncate query的时候，整个表已经是空的了。accountsusersattributesaccount_idNULLaccountsaccounts

那么是如何TRUNCATE TABLE users RESTART IDENTITY CASCADE导致我的属性表根据NULL外键被截断的呢？

postgresql postgresql-9.5