Re: How hard would it be to support LIKE in return declaration of generic record function calls ?

On Wed, 2012-05-02 at 14:32 -0500, Merlin Moncure wrote:
> On Wed, May 2, 2012 at 12:06 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > On ons, 2012-05-02 at 13:40 +0200, Hannu Krosing wrote:
> >> How hard would it be to add support for LIKE syntax, similar to table
> >> def in field list declaration for generic record functions
> >>
> >> What I'dd like to be able to do is to have a generic json_to_record
> >> function
> >>
> >> CREATE OR REPLACE RECORD json_to_record(json) RETURNS RECORD AS $$
> >> ...
> >> $$ LANGUAGE ... ;
> >>
> >> and then be able to call it like this
> >>
> >> insert into test2
> >> select * from json_to_record(jrec json) as (like test2);
> >
> > That would be very useful, and shouldn't be too hard to implement. (I
> > had to look about three times to understand what this was supposed to
> > achieve, but I think the syntax is the right one after all.)
>
> Although I like the functionality, is this better than the trick used
> by hstore/populate_record? That approach doesn't require syntax
> changes and allows you to execute the function without 'FROM'.

You mean the one using a null::returntype for as first argument for
defining a return type of a function:

Convert an hstore to a predefined record type:

CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,
'"col1"=>"456", "col2"=>"zzz"');
col1 | col2 | col3
------+------+------
456 | zzz |
(1 row)

This works the same indeed, just seems to be a hack, though a cool
one :)

hannu=# insert into test
hannu-# SELECT * FROM populate_record(null::test,
'"id"=>"456", "data"=>"zzz"');
INSERT 0 1

putting the same functionality in LIKE at syntax level kind of feels
more orthogonal to table definition:)

select * from to_record(null::mytable, datasource);

vs

select * from to_record(datasource) as (like mytable);

OTOH, we do not support LIKE in type definition either.

If we were to overhaul template-based structure definition, the perhaps
the following syntax woul be better:

create type mytape as mytable; -- exact copy
create type mytape as (like mytable, comment text); -- copy + extra
field

and same for giving type to generic record in function calls.

If it does not mess up the syntax, it would also be good to add simple
casts in record-->table case

select * from to_record(datasource)::mytable;

--
-------
Hannu Krosing
PostgreSQL Unlimited Scalability and Performance Consultant
2ndQuadrant Nordic
PG Admin Book: http://www.2ndQuadrant.com/books/