PostgreSQL的v9.x的有真正的＆QUOT;记录＆QUOT数组;？ [英] PostgreSQL v9.X have real "array of record"?

问题描述

此查询工作正常，

 WITH test AS (
   SELECT array_agg(t) as x FROM (
     SELECT 1111 as id, 'aaaaa' as cc  
   ) AS t
 ) SELECT x[1] FROM test;

但是，我访问recod元素我试图 SELECT X [1] .ID ？; SELECT X [1] [1] ; ......没有什么作品。

but, can I access the recod elements? I try SELECT x[1].id; SELECT x[1][1]; ... nothing works.

PS：与谷歌我们只看到旧的解决方案......上下文这里是v9.x的，没有关于记录数组新闻

PS: with Google we see only OLD solutions... The context here is v9.X, no news about "array of record"?

我也试试。

 select x[1] from (select array[row(1,2)] as x) as t;

没有办法只能访问第1项或仅第2项。

no solution to access only item 1 or only item 2.

一个线索，我无法遵循：<一href=\"http://postgresql.1045698.n5.nabble.com/Arrays-of-records-td1884523.html\">postgresql.1045698.n5.nabble.com解决使用问题CREATE TYPE ...好吧，但我需要所有查询的解决方案。哪里的PostgreSQL的动态类型？如何PSS类型转换或前$ P $没有 CREATE TYPE 条款？

A clue that I was unable to follow: postgresql.1045698.n5.nabble.com solve the problem using a CREATE TYPE... Ok, but I need "all in a query" solution. Where the "dynamic typing" of PostgreSQL?? How to CAST or express the type without a CREATE TYPE clause?

推荐答案

在present有没有出现任何语法来访问匿名类型的记录，除了通过函数调用的语法或通过hstore。那除非有人谁真正关心到来，使之发生是不幸的，但不太可能得到固定着急。还有其他优先事项。

At present there does not appear to be any syntax for accessing a record of anonymous type except via the function call syntax or via hstore. That's unfortunate, but not likely to get fixed in a hurry unless someone who really cares comes along to make it happen. There are other priorities.

您有三种解决方法选择：

You have three workaround options:

• CREATE TYPE


• hstore

• CREATE TYPE
• hstore

问题是具有匿名类型的记录。因此，使它不能匿名。不幸的是它成为一个匿名的记录类型之前，这仅仅是可能的;您目前无法从记录转换为用户类型。所以，你需要做的：

The issue is with records of anonymous type. So make it not anonymous. Unfortunately this is only possible before it becomes an anonymous record type; you can't currently cast from record to a usertype. So you'd need to do:

CREATE TYPE some_t AS (id integer, cc text);

WITH test AS (
   SELECT array_agg(t::some_t) as x FROM (
     SELECT 1111 as id, 'aaaaa' as cc  
   ) AS t
 ) SELECT x[1].id FROM test;

请注意子查询输出的强制转换为 some_t 聚合前。

Note the cast of the subquery output to some_t before aggregation.

我不能说我明白，为什么不能索引数组，而不是之后进行这种转换。

I can't say I understand why this cast can't be performed after indexing the array instead.

像往常一样， hstore 游乐设施大多救援困难类型的问题。

As usual, hstore rides to the mostly-rescue with difficult type problems.

regress=> WITH test AS (
       SELECT array_agg(t) as x FROM (
         SELECT 1111 as id,  'aaaaa' as cc  
       ) AS t
     ) SELECT hstore(x[1])->'id' FROM test;
 ?column? 
----------
 1111
(1 row)

您需要的 hstore 扩展，我敢肯定它的效率不高，但它的作品。这是建立在 hstore 支持创建从已添加到支持新和<$ C $匿名记录hstore C> OLD <在触发器/ code>，过去的痛点。

You need the hstore extension, and I'm sure it's not efficient, but it works. This builds on the hstore support for creating a hstore from anonymous records that was added to support NEW and OLD in triggers, a past pain-point.

原来你不能左右它得到一个简单的包装功能，让你指定的呼叫现场的类型：

Turns out you can't get around it with a simple wrapper function to let you specify the type on the call-site:

regress=> CREATE OR REPLACE FUNCTION identity(record) RETURNS record AS $$
          SELECT $1; 
          $$ LANGUAGE sql IMMUTABLE;
ERROR:  SQL functions cannot have arguments of type record

所以你必须使用一个更高的开销过程语言，此时你还不如用hstore相反，它会更快，更容易。

so you'd have to use a higher-overhead procedural language, at which point you might as well use hstore instead, it'll be faster and easier.

所以，这是所有有点难看。它不是永远可能是可以直接从一个匿名记录索引的字段，因为它可能不存在，并且它的类型不能推断。但是，没有理由我们不能使用类型系统的功能，使我们能够从一个函数返回记录和主叫方指定其类型也蒙上这样做

So, this is all a bit ugly. It's not ever likely to be possible to directly index a field from an anonymous record, since it might not exist and its type cannot be deduced. But there's no reason we can't use the type system feature that allows us to return record from a function and specify its type on the caller-side to also do so in casts.

应该有可能使PostgreSQL的支持是这样的：

It should be possible to make PostgreSQL support something like:

WITH test AS (
   SELECT array_agg(t) as x FROM (
      SELECT 1111 as id,  'aaaaa' as cc  
   ) AS t
) SELECT (x[1] AS some_t(id integer, cc text)).id  FROM test;

它刚刚涉及相应的解析器黑客和方式，以确保这是从来没有含糊解析冲突与列别名。

it'd just involve appropriate parser-hacking, and a way to make sure that was never ambiguously parsed in conflict with a column-alias.

真的，即使类型推断可能是可能的，如果有人愿意把工作和说服团队，需要相当大量的查询筹办处理器时间是值得的。 （不太可能）。

Really, even type-inference could be possible if someone was willing to put the work in and convince the team that the rather large amount of query-planner processor time required was worth it. (unlikely).

这是一个刺激，但小的，角落里的类型系统。如果你想要改变你将需要作出的pgsql-隔音一般，并愿意做实事，以改善这个问题伴随着噪音。这可能需要学习更多比你想要了解PostgreSQL的类型系统的内部结构，学习的向后兼容的乐趣，并在圈一圈又一圈有令人沮丧的论据。欢迎开源！

This is an irritating, but minor, corner in the type system. If you want it to change you will need to make noise on pgsql-general, and accompany that noise with the willingness to do real work to improve the problem. This may involve learning more than you ever wanted to know about the innards of PostgreSQL's type system, learning the fun of "backward compatibility", and having frustrating arguments around and around in circles. Welcome to open source!

这篇关于PostgreSQL的v9.x的有真正的＆QUOT;记录＆QUOT数组;？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！