如何用node-postgres设置模式[英] How to set schema with node-postgres

本文是小编为大家收集整理的关于如何用node-postgres设置模式的处理方法,想解了如何用node-postgres设置模式的问题怎么解决?如何用node-postgres设置模式问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我正在尝试使用 node-postgres 在我的 postgres 数据库中查询名为 DOCUMENT 的模式.

我似乎无法获得针对指定架构运行的查询.

这个查询使用 psql 直接针对 postgres 运行良好

SELECT * FROM "DOCUMENT".document_metadata m 
LEFT OUTER JOIN "DOCUMENT".document_attributes a 
ON a.document_id = m.id

此代码产生以下错误

const query = `SELECT * FROM "DOCUMENT".document_metadata m 
               LEFT OUTER JOIN "DOCUMENT".document_attributes a 
               ON a.document_id = m.id`
const metadata = await db.query(query)

错误

error: relation "DOCUMENT.document_metadata" does not exist
    at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:602:11)
    at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

我也试过了

const query = `SET search_path TO 'DOCUMENT';
               SELECT * FROM document_metadata m 
               LEFT OUTER JOIN document_attributes a 
               ON a.document_id = m.id;`

const metadata = await db.query(query)

产生错误

error: relation "document_metadata" does not exist
    at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:602:11)
    at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

更新当我直接在 psql 中运行这些相同的查询时,我看到 10 行带有表名.当我通过我的节点代码运行它时,我看不到任何行:

const metadata4 = await db.query('SHOW search_path;')
console.log('search_path after setting = ', metadata4.rows) // prints [ { search_path: '"DOCUMENT"' } ]

const tables = await db.query(`SELECT * FROM information_schema.tables where table_schema = 'DOCUMENT';`)
console.log('tables = ', tables.rows) // prints []

推荐答案

只在单独的行中执行:

db.query("SET search_path TO 'DOCUMENT';")

DOCUMENT 是你的模式的名称,然后执行普通查询:

const query = `SELECT * FROM document_metadata m 
               LEFT OUTER JOIN document_attributes a 
               ON a.document_id = m.id;`

db.query(query)

本文地址:https://www.itbaoku.cn/post/1764186.html