Thread: need of anonymous record
My notion of "anonymous record", and the need of this kind of "higher-order type", are discussed in the links below,
"Functions can not to return individual items of a record"
"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?
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.
Craig Ringer shows another suggestion in the (second) link.
I am new here, I do not know how, but I'm available to help... Can a future version of PostgreSQL implement some solution?
Peter
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