Postgres中的动态SQL查询 [英] dynamic sql query in postgres

问题描述

我试图使用动态SQL在postgres中运行一些查询.

I was attempting to use Dynamic SQL to run some queries in postgres.

示例:

EXECUTE format('SELECT * from result_%s_table', quote_ident((select id from ids where condition = some_condition)))

我必须查询一个表，该表的格式为result_％s_table，其中，我需要从另一个表中替换正确的表名(ID).

I have to query a table, which is of the form result_%s_table wherein, I need to substitute the correct table name (an id) from an another table.

我收到错误ERROR: prepared statement "format" does not exist

链接:用查询结果postgresql替换字符串

推荐答案

EXECUTE ... USING仅在 PL/PgSQL -即在函数内或 <用PL/PgSQL语言编写的c2>块.它在纯SQL中不起作用；普通SQL中的EXECUTE与执行准备好的语句完全不同.您不能直接在PostgreSQL的SQL方言中使用动态SQL.

EXECUTE ... USING only works in PL/PgSQL - ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.

比较:

• PL/PgSQL的EXECUTE ... USING ;
• SQL的EXECUTE

请参见我之前的回答中倒数第二个.

除了在PL/PgSQL中不运行外，您的SQL语句是错误的，它不会执行您期望的操作.如果(select id from ids where condition = some_condition)返回说42，则如果id是整数，则该语句将失败.如果将其转换为文本，则会得到:

In addition to not running except in PL/PgSQL your SQL statement is wrong, it won't do what you expect. If (select id from ids where condition = some_condition) returns say 42, the statement would fail if id is an integer. If it's cast to text you'd get:

EXECUTE format('SELECT * from result_%s_table', quote_ident('42'));
EXECUTE format('SELECT * from result_%s_table', '"42"');
EXECUTE 'SELECT * from result_"42"_table';

那是无效的.您实际上需要result_42_table或"result_42_table".您将不得不编写更多类似的内容:

That's invalid. You actually want result_42_table or "result_42_table". You'd have to write something more like:

EXECUTE format('SELECT * from %s', quote_ident('result_'||(select id from ids where condition = some_condition)||'_table'))

...，如果您必须使用quote_ident.

... if you must use quote_ident.

这篇关于Postgres中的动态SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！