json - 如何在 PostgreSQL 中使用 row_to_json 分配 JSON 变量
问题描述
我正在尝试在 PostgreSQL 中创建一个匿名函数来为应用程序创建模拟数据。我想先做一个 SELECT 查询(从随机包中获取数据),然后使用 row_to_json 将所有行转换为 JSON,然后将结果分配给 JSON 类型的变量。
我需要此包机信息,以便将其添加到预订表中。
这不起作用,我想我不知道如何将选择的结果与之前创建的变量相关联;我收到charterData为空的错误,我想知道如何实现这一点..
这是 SQL 中的匿名函数:
BEGIN;
DO $$
DECLARE charterData JSON;
DECLARE bookingId INTEGER;
BEGIN
SELECT row_to_json(t) INTO charterData FROM (select charter_id, name from charters) t WHERE charter_id = 1;
INSERT INTO bookings (charter, yacht, email, date, guests, total, start_hour, end_hour, hotel, arrival_date) values (charterData, '{"test":1}', 'a', '12/10/1995', 8, '78', '123', '123', '123', '123')
RETURNING booking_id INTO bookingId;
END $$;
COMMIT;
表宪章:
Table "public.charters"
Column | Type | Collation | Nullable | Default
-------------+-------------------+-----------+----------+----------------------------------------------
charter_id | integer | | not null | nextval('charters_charter_id_seq'::regclass)
name | character varying | | not null |
description | character varying | | not null |
sail_hours | integer | | not null |
Indexes:
"charters_pk" PRIMARY KEY, btree (charter_id)
"name_charter" UNIQUE CONSTRAINT, btree (name)
Referenced by:
TABLE "bookings" CONSTRAINT "charters_bookings_fk" FOREIGN KEY (charter) REFERENCES charters(name) ON DELETE CASCADE
TABLE "pricing" CONSTRAINT "charters_pricing_fk" FOREIGN KEY (charter_id) REFERENCES charters(charter_id) ON DELETE CASCADE
预订表:
Table "public.bookings"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+----------------------------------------------
booking_id | integer | | not null | nextval('bookings_booking_id_seq'::regclass)
charter | json | | not null |
yacht | json | | not null |
email | character varying | | not null |
date | date | | not null |
guests | integer | | not null |
total | numeric | | not null |
start_hour | character varying | | not null |
end_hour | character varying | | not null |
alcohol | character varying | | |
transportation | character varying | | |
others | character varying | | |
arrival_date | character varying | | |
hotel | character varying | | |
Indexes:
"bookings_pk" PRIMARY KEY, btree (booking_id)
"end_hour" UNIQUE CONSTRAINT, btree (end_hour)
"start_hour" UNIQUE CONSTRAINT, btree (start_hour)
Foreign-key constraints:
"charters_bookings_fk" FOREIGN KEY (charter) REFERENCES charters(name) ON DELETE CASCADE
"yachts_bookings_fk" FOREIGN KEY (yacht) REFERENCES yachts(name) ON DELETE CASCADE
Referenced by:
TABLE "bookings_extra" CONSTRAINT "bookings_extra_fk" FOREIGN KEY (booking_id) REFERENCES bookings(booking_id) ON DELETE CASCADE
解决方案
好的,我找到了答案......有点傻,但也许这个答案会对某人有所帮助
BEGIN;
DO $$
DECLARE charter JSON;
DECLARE bookingId INTEGER;
BEGIN
charter := (SELECT row_to_json(t) FROM (SELECT charter_id, name FROM charters) t WHERE charter_id = $1);
INSERT INTO bookings
(charter, yacht, email, date, passengers, total, start_hour, end_hour, hotel, arrival_date, charter_price)
values (charter, '{"test":1}', 'a', '12/10/1995', 8, '78', '123', '123', '123', '123', '132')
RETURNING booking_id INTO bookingId;
END $$;
COMMIT;
推荐阅读
- python - 是否可以在命令行中使用带有 argparse 的空格?前任。--name 名字 姓氏
- arduino-esp32 - 与 microSD 卡交互时出错?
- ios - 无法监督设备 - Apple tv 设置
- javascript - Jquery html不适用于动态添加的元素
- math - (算法 | NetLogo)如何根据坐标输入创建和填充多边形?
- hazelcast - HazelcastSerializationException 与 Hazelcast 4.2 和 bucket4j 6.2.0
- php - 我们可以在 PHP 7.3 或任何最新版本上运行 Zend 框架 2.0.5 或 2.xx 版本吗?
- java - 如何从 xml 文件迁移到编程配置
- google-cloud-pubsub - 如何使用 Google 监控查询语言从不同的资源类型中获取指标?
- android - 如何从材料日期选择器中选择数据并替换编辑文本字段中的选定日期?