查询结果的字符串替换PostgreSQL [英] string substitution with query result postgresql

问题描述

我有一个查询，该查询将始终只返回一个元素。我想将此查询的结果附加到可以用于进一步处理的字符串中

I have a query which will always return only one element as a result. I want to append the result of this query into a string which I can use for futher processing

示例

从具有某些条件的ID中选择ID

我想将此ID附加在字符串中

I want to append this id in a string

类似于 result_（id）_table ，其中，必须用上一个查询返回的ID替换ID（本质上是另一个表）

like result_(id)_table, where, the id has to be substituted with the id returned from the previous query (Which is essentialy an another table)

最后，我应该能够执行类似

and finally, I should be able to execute a query like

select * from result_id_table

其中， result_id_table是其中id为的表的名称

Where, "result_id_table" is the name of the table where id is appropriately substituted

推荐答案

使用字符串连接：

-- Demo table structure
CREATE TABLE dummy ( id integer primary key, blah integer not null);
INSERT INTO dummy(id, blah) VALUES (1,1);

-- Single-valued query
SELECT id FROM dummy WHERE blah = 1;

-- Formatted with string concatenation
SELECT 'result_'||(id::text)||'_table' FROM dummy WHERE blah = 1;

-- Formatted using the `format` function
SELECT format('result_%s_table', id) FROM dummy WHERE blah = 1;

如果要收集其他信息，请使用子查询将其合并为字符串

Combine into string using subquery if you're collecting other information

SELECT 'result_'||(SELECT id FROM dummy WHERE blah = 1)||'_table'
FROM .... WHERE ...

或使用联接。

您的编辑建议您然后将其用作表名。这可能意味着您的设计不好。而不是：

Your edit suggests you want to then use that as a table name. This probably means your design is bad. Instead of:

CREATE TABLE sometable_1 ( id integer primary key, ...);
CREATE TABLE sometable_2 ( id integer primary key, ...);
CREATE TABLE sometable_3 ( id integer primary key, ...);
...
CREATE TABLE sometable_n ( id integer primary key, ...);

您几乎总能从以下方面受益：

you're almost always better off with:

CREATE TABLE sometable(
     id integer not null,
     discriminator integer not null,
     primary key (id, discriminator),
     ...
);

或每个模式表。如果由于某种原因而对这种方法感到困惑，则可以使用PL / PgSQL的 EXECUTE 语句来运行动态SQL，例如：

or per-schema tables. If for some reason you're stuck with this approach you can use PL/PgSQL's EXECUTE statement to run dynamic SQL, like:

EXECUTE format('SELECT * FROM sometable_%s WHERE blah = ?', 
            quote_ident((SELECT id FROM dummy WHERE blah = 1))
        )
USING 2;

查询 sometable_1中 blah = 2的行。手册中的更多信息，请参见 EXECUTE ...使用 。

to query "sometable_1" for rows where "blah = 2". More info in the manual, see EXECUTE ... USING.

在常规SQL for PostgreSQL中根本不可能做到这一点。在应用程序或PL / PgSQL中执行。可以使用PL / PgSQL DO 块，但是如果您对所有内容都依赖，则性能会很糟糕。

It is simply not possible to do this in regular SQL for PostgreSQL. Do it in the application, or in PL/PgSQL. A PL/PgSQL DO block can be used, but performance will be awful if you're relying on that for everything.

在为时已晚之前修复您的设计。

Fix your design now, before it's too late.

这篇关于查询结果的字符串替换PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！