Обсуждение: need of anonymous record

Peter Padua Krauss wrote
> The first question is about performance: "*returns table*" have the same
> performance than "*returns record*"??
> If "yes", the *record* datatype is somewhat outdated?

Table defines the possibility to return a set while record can only ever
return a single value; so likely the set version is less performant but
regardless they have different semantics.


> I am new here, I do not know how, but I'm available to help... Can a
> future
> version of PostgreSQL implement some solution?

Craig's reply in the array post explained the reality quite well.  SQL is
strongly typed so while some improvement on the use of anonymous record
types is possible the SQL way of doing things would be to CREATE TYPE. 
Hstore and JSON provides an alternative means to define a column in a
dynamic way.  

It is unlikely that records types would ever be altered for dynamic member
access given that these types exist. But it is not impossible given a good
implementation. The one benefit is that current attempts give errors so
making it work only has to avoid performance regressions and not screw up
the parser.

As Craig said: how much does this bother you? If you cannot code C yourself
are you willing to pay someone to implement this feature?  The cost-benefit
analysis suggests this particular need is a poor area to invest resources
in.

David J.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/need-of-anonymous-record-tp5802301p5802303.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 05/03/2014 09:55 AM, Peter Krauss wrote:
> My notion of "anonymous record", and the need of this kind of 
> "higher-order type", are discussed in the links below,
>
> http://stackoverflow.com/q/23439240
>   "Functions can not to /return individual items of a record/"
>
> http://stackoverflow.com/q/21246201
>   "PostgreSQL v9.X have real '/array of record/' ?
>
> The first question is about performance: "/returns table/" have the 
> same performance than "/returns record/"??
> If "yes", the /record/ datatype is somewhat outdated?

Quite apart from other considerations such as the clear lack of 
understanding of semantics here, I at least am not going to follow 
threads to stackoverflow. If you want to discuss the actual or desired 
semantics of Postgres, the proper place is the Postgres mailing lists.

cheers

andrew

On 05/03/2014 09:55 PM, Peter Krauss wrote:

> If "yes", the /record/ datatype is somewhat outdated?

No, it isn't.

`RETURNS TABLE` is functionally the same as `RETURNS SETOF RECORD` with
`OUT` parameters. However, `RETURNS SETOF RECORD` can return arbitrary
records of no fixed structure too, and that's something `RETURNS TABLE`
cannot do.

So there's no alternative to 'record' for things like the crosstab function.

It's also important and useful for things like loop variables in PL/PgSQL.

> The second question/discussion shows a more deeper problem, where
> perhaps the use of string index in array syntax (something like x['a'])
> would be a good syntax solution.

The title of that is completely misleading, it's nothing to do with arrays.

What you appear to want is to access arbitrary fields of a record by name.

The reason this isn't supported directly in PL/PgSQL is, AFAIK, mainly
an issue of data typing. Each field in a record may be of a different
type. So the return type would change based on the field accessed at
execution time, which isn't something PL/PgSQL or regular SQL likes very
much, as plans are formed and cached in part based on the types involved.

We could and probably should provide an operator or function to access
records of a field in their text form by name. Patches are welcome ;-)

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Craig Ringer <craig@2ndquadrant.com> writes:
> What you appear to want is to access arbitrary fields of a record by name.

> The reason this isn't supported directly in PL/PgSQL is, AFAIK, mainly
> an issue of data typing. Each field in a record may be of a different
> type. So the return type would change based on the field accessed at
> execution time, which isn't something PL/PgSQL or regular SQL likes very
> much, as plans are formed and cached in part based on the types involved.

Right.  For better or worse, SQL is a strongly-typed language.  PG's
emphasis on datatype extensibility makes this constraint even tighter,
since you can't even tell e.g. which operators or functions would be
valid to apply to a field of indeterminate type.

> We could and probably should provide an operator or function to access
> records of a field in their text form by name. Patches are welcome ;-)

As was noted upthread, if you're satisfied with pretending all fields
of a record are text, it's not clear why you wouldn't be happy using
hstore instead.  Or JSON, which at least has heard of numbers as well
as text.  I think the current push to improve json/jsonb is driven
largely by the desire to handle scenarios of this sort.
        regards, tom lane