选择性PostgreSQL数据库查询 [英] Selective PostgreSQL database querying

问题描述

是否可以在PostgreSQL中进行选择查询，以根据已选择的行的值选择不同的表/列？

Is it possible to have selective queries in PostgreSQL which select different tables/columns based on values of rows already selected?

基本上，我有一个表其中每行包含一个由2到5个字符组成的序列（ tbl_roots ），还可以选择带有一个长度字段，该字段指定该序列应包含的字符数（用于一旦找到更好的方法（例如，通过计算序列的长度），就可以避免冗余。

Basically, I've got a table in which each row contains a sequence of two to five characters (tbl_roots), optionally with a length field which specifies how many characters the sequence is supposed to contain (it's meant to be made redundant once I figure out a better way, i.e. by counting the length of the sequences).

有四个包含模式的表（ tbl_patterns_biliteral ， tbl_patterns_triliteral ，... etc），每个对应于 root_length ，以及第五个表（ tbl_patterns ）用于通过为每行提供一个标识符来同步模式表-因此 tbl_patterns_biliteral 中的第2行对应于 tbl_patterns_triliteral 。六个模式表受到限制，以使 tbl_patterns_（bi | tri | quadri | quinqui）literal 中的行都不能具有 pattern_id tbl_patterns 中不存在的c>。

There are four tables containing patterns (tbl_patterns_biliteral, tbl_patterns_triliteral, ...etc), each of which corresponds to a root_length, and a fifth table (tbl_patterns) which is used to synchronise the pattern tables by providing an identifier for each row—so row #2 in tbl_patterns_biliteral corresponds to the same row in tbl_patterns_triliteral. The six pattern tables are restricted such that no row in tbl_patterns_(bi|tri|quadri|quinqui)literal can have a pattern_id that doesn't exist in tbl_patterns.

每个模式表还具有其他九个列，分别对应于一个标识符（ root_form ）。

Each pattern table has nine other columns which corresponds to an identifier (root_form).

数据库中的最后一个表（ tbl_words ）包含每个主表的列（ word_id ， root_id ， pattern_id ， root_form ， word ）。每个单词都被定义为特定长度和形式的词根，并拼接成特定模式。拼接相对简单： translate（pattern，'12345'，array_to_string（root，''））就像word_combined 一样。

The last table in the database (tbl_words), contains a column for each of the major tables (word_id, root_id, pattern_id, root_form, word). Each word is defined as being a root of a particular length and form, spliced into a particular pattern. The splicing is relatively simple: translate(pattern, '12345', array_to_string(root, '')) as word_combined does the job.

现在，我要根据 tbl_roots 中序列的长度选择适当的模式表，然后在模式中选择适当的列表根据 root_form 的值。

Now, what I want to do is select the appropriate pattern table based on the length of the sequence in tbl_roots, and select the appropriate column in the pattern table based on the value of root_form.

这怎么办？可以将它组合成一个简单的查询，还是需要多次通过？建立完此查询后，便可以将其编码为可以搜索数据库的PHP脚本。

How could this be done? Can it be combined into a simple query, or will I need to make multiple passes? Once I've built up this query, I'll then be able to code it into a PHP script which can search my database.

编辑 >

这里有一些示例数据（实际上是我目前正在使用的数据）以及有关系统工作方式的更多说明： https://gist.github.com/823609

Here's some sample data (it's actually the data I'm using at the moment) and some more explanations as to how the system works: https://gist.github.com/823609

从概念上讲，它更简单比起初看起来要好，尤其是如果您将其视为坐标系。

It's conceptually simpler than it appears at first, especially if you think of it as a coordinate system.

推荐答案

我认为您将拥有改变表的结构，以期产生希望。这是供您考虑的初稿。我不确定列名中 i， ii和 iii的含义是什么。由于无知，我假设它们对您很有意义，因此将其保存在下表中。 （我将它们的信息保存为整数。如果需要，可以很容易地将其更改为小写罗马数字。）

I think you're going to have to change the structure of your tables to have any hope. Here's a first draft for you to think about. I'm not sure what the significance of the "i", "ii", and "iii" are in your column names. In my ignorance, I'm assuming they're meaningful to you, so I've preserved them in the table below. (I preserved their information as integers. Easy to change that to lowercase roman numerals if it matters.)

create table patterns_bilateral (
  pattern_id integer not null,
  root_num integer not null,
  pattern varchar(15) not null,
  primary key (pattern_id, root_num)
);

insert into patterns_bilateral values
(1,1, 'ya1u2a'), 
(1,2, 'ya1u22a'),
(1,3, 'ya12u2a'), 
(1,4, 'me11u2a'), 
(1,5, 'te1u22a'), 
(1,6, 'ina12u2a'), 
(1,7, 'i1u22a'), 
(1,8, 'ya1u22a'), 
(1,9, 'e1u2a');

我敢肯定这样的结构会更容易查询，但是您知道您的字段比我好。 （另一方面，数据库设计是我的领域...）

I'm pretty sure a structure like this will be much easier to query, but you know your field better than I do. (On the other hand, database design is my field . . . )

扩大我之前的回答和评论，看一下这个查询。 （测试表甚至不在3NF中，但该表现在并不重要。）

Expanding on my earlier answer and our comments, take a look at this query. (The test table isn't even in 3NF, but the table's not important right now.)

create table test (
root_id integer,
root_substitution varchar[],
length integer,
form integer,
pattern varchar(15),
primary key (root_id, length, form, pattern));

insert into test values
(4,'{s,ş,m}', 3, 1, '1o2i3');

这是重要部分。

select root_id
     , root_substitution
     , length
     , form
     , pattern
     , translate(pattern, '12345', array_to_string(root_substitution, '')) 
from test;

该查询除其他外返回翻译soşim。

That query returns, among other things, the translation soşim.

我们正在朝正确的方向前进吗？

Are we heading in the right direction?

这篇关于选择性PostgreSQL数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！