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

Hi Hackers

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);

ERROR:  syntax error at or near "like"

instead of explicitly spelling out the structure of table test2 in the
AS part.

insert into test2 
select * from json_to_record(jrec json)                        as (id int, data2 test, tstamp timestamp);
INSERT 0 1


PS.

As a pie-in-the-sky wish I'd prefer of course even simpler syntax of

insert into test2 json_to_record(jrec json);

or at least  

insert into test2 json_to_record(jrec json)::test2;

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

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.)

On Wed, May 2, 2012 at 12:06 PM, Peter Eisentraut <peter_e@gmx.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'.

merlin

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@gmx.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/

Hello

> (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

few years back I proposed "anytypename" type

with this feature, you can has some clean and more readable call

SELECT * FROM populate_record(test, ...)

Regards

Pavel

On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
>> (1 row)
>>
>> This works the same indeed, just seems to be a hack, though a cool
>> one :)

Yeah -- the syntax isn't great, but IMO it's more generally usable
than what you're proposing because it's a scalar returning function
not a table expression.  Another point is that the proposed 'like'
syntax (which I still think is great, just maybe not for conversions
from json) seems wedded to record types.  The hstore trick should be
able to take a foo[], set it all up and return it.  How would that
work with like?

> few years back I proposed "anytypename" type
>
> with this feature, you can has some clean and more readable call
>
> SELECT * FROM populate_record(test, ...)

that would be great IMO.

merlin

2012/5/3 Merlin Moncure <mmoncure@gmail.com>:
> On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> Hello
>>
>>> (1 row)
>>>
>>> This works the same indeed, just seems to be a hack, though a cool
>>> one :)
>
> Yeah -- the syntax isn't great, but IMO it's more generally usable
> than what you're proposing because it's a scalar returning function
> not a table expression.  Another point is that the proposed 'like'
> syntax (which I still think is great, just maybe not for conversions
> from json) seems wedded to record types.  The hstore trick should be
> able to take a foo[], set it all up and return it.  How would that
> work with like?
>
>> few years back I proposed "anytypename" type
>>
>> with this feature, you can has some clean and more readable call
>>
>> SELECT * FROM populate_record(test, ...)
>
> that would be great IMO.

I'll try propose it again - implementation should not be hard

Regards

Pavel
>
> merlin

On 05/03/2012 09:43 AM, Pavel Stehule wrote:
> 2012/5/3 Merlin Moncure<mmoncure@gmail.com>:
>> On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule<pavel.stehule@gmail.com>  wrote:
>>> Hello
>>>
>>>> (1 row)
>>>>
>>>> This works the same indeed, just seems to be a hack, though a cool
>>>> one :)
>> Yeah -- the syntax isn't great, but IMO it's more generally usable
>> than what you're proposing because it's a scalar returning function
>> not a table expression.  Another point is that the proposed 'like'
>> syntax (which I still think is great, just maybe not for conversions
>> from json) seems wedded to record types.  The hstore trick should be
>> able to take a foo[], set it all up and return it.  How would that
>> work with like?
>>
>>> few years back I proposed "anytypename" type
>>>
>>> with this feature, you can has some clean and more readable call
>>>
>>> SELECT * FROM populate_record(test, ...)
>> that would be great IMO.
> I'll try propose it again - implementation should not be hard
>
>

You guys seem to be taking the original proposal off into the weeds. I 
have often wanted to be able to use LIKE in type expressions, and I'd 
like to see exactly that implemented.

cheers

andrew

On Thu, May 3, 2012 at 9:01 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> On 05/03/2012 09:43 AM, Pavel Stehule wrote:
>>
>> 2012/5/3 Merlin Moncure<mmoncure@gmail.com>:
>>>
>>> On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule<pavel.stehule@gmail.com>
>>>  wrote:
>>>>
>>>> Hello
>>>>
>>>>> (1 row)
>>>>>
>>>>> This works the same indeed, just seems to be a hack, though a cool
>>>>> one :)
>>>
>>> Yeah -- the syntax isn't great, but IMO it's more generally usable
>>> than what you're proposing because it's a scalar returning function
>>> not a table expression.  Another point is that the proposed 'like'
>>> syntax (which I still think is great, just maybe not for conversions
>>> from json) seems wedded to record types.  The hstore trick should be
>>> able to take a foo[], set it all up and return it.  How would that
>>> work with like?
>>>
>>>> few years back I proposed "anytypename" type
>>>>
>>>> with this feature, you can has some clean and more readable call
>>>>
>>>> SELECT * FROM populate_record(test, ...)
>>>
>>> that would be great IMO.
>>
>> I'll try propose it again - implementation should not be hard
>>
>>
>
> You guys seem to be taking the original proposal off into the weeds. I have
> often wanted to be able to use LIKE in type expressions, and I'd like to see
> exactly that implemented.

would it work for array types?  can it called without using FROM?

merlin

Andrew Dunstan <andrew@dunslane.net> writes:
> You guys seem to be taking the original proposal off into the weeds. I 
> have often wanted to be able to use LIKE in type expressions, and I'd 
> like to see exactly that implemented.

This notion of "anytypename" is utterly unworkable anyway; there's no
way for the parser to know soon enough that a given argument position
needs to be read as a type name rather than a normal expression.
You could conceivably make it work with the argument being a regtype
literal (ie, quoted); but that seems at least as klugy as what hstore
is doing.
        regards, tom lane

2012/5/3 Tom Lane <tgl@sss.pgh.pa.us>:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> You guys seem to be taking the original proposal off into the weeds. I
>> have often wanted to be able to use LIKE in type expressions, and I'd
>> like to see exactly that implemented.
>
> This notion of "anytypename" is utterly unworkable anyway; there's no
> way for the parser to know soon enough that a given argument position
> needs to be read as a type name rather than a normal expression.
> You could conceivably make it work with the argument being a regtype
> literal (ie, quoted); but that seems at least as klugy as what hstore
> is doing.
>

type identifier is same identifier like other - but I have no
prototype now, so I don't know if there is some trap

I remember so I though about using CAST keyword too

some like SELECT CAST( populate_record(hstore_value) AS type)

Regards

Pavel

>                        regards, tom lane

On 05/03/2012 10:18 AM, Merlin Moncure wrote:
> On Thu, May 3, 2012 at 9:01 AM, Andrew Dunstan<andrew@dunslane.net>  wrote:
>> On 05/03/2012 09:43 AM, Pavel Stehule wrote:
>>> 2012/5/3 Merlin Moncure<mmoncure@gmail.com>:
>>>> On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule<pavel.stehule@gmail.com>
>>>>   wrote:
>>>>> Hello
>>>>>
>>>>>> (1 row)
>>>>>>
>>>>>> This works the same indeed, just seems to be a hack, though a cool
>>>>>> one :)
>>>> Yeah -- the syntax isn't great, but IMO it's more generally usable
>>>> than what you're proposing because it's a scalar returning function
>>>> not a table expression.  Another point is that the proposed 'like'
>>>> syntax (which I still think is great, just maybe not for conversions
>>>> from json) seems wedded to record types.  The hstore trick should be
>>>> able to take a foo[], set it all up and return it.  How would that
>>>> work with like?
>>>>
>>>>> few years back I proposed "anytypename" type
>>>>>
>>>>> with this feature, you can has some clean and more readable call
>>>>>
>>>>> SELECT * FROM populate_record(test, ...)
>>>> that would be great IMO.
>>> I'll try propose it again - implementation should not be hard
>>>
>>>
>> You guys seem to be taking the original proposal off into the weeds. I have
>> often wanted to be able to use LIKE in type expressions, and I'd like to see
>> exactly that implemented.
> would it work for array types?  can it called without using FROM?



Why would you always need FROM? I want to be able to do things like:
    create type new_type as (like old_type, extra text);

i.e., anywhere we are specifying a type (e.g. as above or for a function 
returnign a setof record), we should be able to import an existing one 
rather than having to replicate it.

cheers

andrew

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2012/5/3 Tom Lane <tgl@sss.pgh.pa.us>:
>> This notion of "anytypename" is utterly unworkable anyway; there's no
>> way for the parser to know soon enough that a given argument position
>> needs to be read as a type name rather than a normal expression.

> type identifier is same identifier like other - but I have no
> prototype now, so I don't know if there is some trap

No, it isn't, at least not if you have any ambition to support array
types for instance; to say nothing of types whose standard names are
keywords, multiple words, etc.  Even if you were willing to restrict the
feature to only work for simple-identifier type names, the parser would
have thrown an error for failing to find a column by that name, or else
would have misinterpreted the type name as a column name, long before
there is any opportunity to recognize that the argument position is
an "anytypename" argument.
        regards, tom lane

On Thu, May 3, 2012 at 9:44 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> Why would you always need FROM?

that was coming from Hannu's original example:
insert into test2
select * from json_to_record(jrec json) as (like test2);

how do you work it so you can call:
select json_to_record(jrec json) as (like test2);
select json_to_array(jrec_json) as ??

>    create type new_type as (like old_type, extra text);

sure, that would be great on it's own merits.

merlin

2012/5/3 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2012/5/3 Tom Lane <tgl@sss.pgh.pa.us>:
>>> This notion of "anytypename" is utterly unworkable anyway; there's no
>>> way for the parser to know soon enough that a given argument position
>>> needs to be read as a type name rather than a normal expression.
>
>> type identifier is same identifier like other - but I have no
>> prototype now, so I don't know if there is some trap
>
> No, it isn't, at least not if you have any ambition to support array
> types for instance; to say nothing of types whose standard names are
> keywords, multiple words, etc.  Even if you were willing to restrict the
> feature to only work for simple-identifier type names, the parser would
> have thrown an error for failing to find a column by that name, or else
> would have misinterpreted the type name as a column name, long before
> there is any opportunity to recognize that the argument position is
> an "anytypename" argument.

we can identify a position "anytypename" before raising error - it can
be similar to current identification of PL/pgSQL variables inside
expression. Probably it is too complex for this issue :(

Maybe some keyword can help to us. What do you think about new
operator TYPE that can returns regtype value and can be used together
with polymorphic functions.

CREATE FUNCTION foo(anyregtype, ....)
RETURNS anyelement AS ..

SELECT foo('mytype', ....)

or

SELECT foo(TYPE mytype, ....)

It is little bit cleaner than NULL::type.

Regards

Pavel

>
>                        regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2012/5/3 Tom Lane <tgl@sss.pgh.pa.us>:
>> No, it isn't, at least not if you have any ambition to support array
>> types for instance; to say nothing of types whose standard names are
>> keywords, multiple words, etc.

> we can identify a position "anytypename" before raising error - it can
> be similar to current identification of PL/pgSQL variables inside
> expression. Probably it is too complex for this issue :(

[ shrug ... ]  Feel free to spend time that way if you want to, but
I'm entirely confident that you won't come out with anything except
an ugly, unmaintainable, incomplete kluge.

> Maybe some keyword can help to us. What do you think about new
> operator TYPE that can returns regtype value and can be used together
> with polymorphic functions.

Doesn't have any more attraction for me than the proposed LIKE
extension; that will have the same results and it's at least traceable
to SQL-standard notations.
        regards, tom lane

On Thu, May 3, 2012 at 10:12 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Maybe some keyword can help to us. What do you think about new
>> operator TYPE that can returns regtype value and can be used together
>> with polymorphic functions.
>
> Doesn't have any more attraction for me than the proposed LIKE
> extension; that will have the same results and it's at least traceable
> to SQL-standard notations.

no it won't (unless I'm being completely obtuse in addition to being
repetitive): LIKE only works when you treat your function call as a
table expression: FROM func() AS ...

that's fine, but converting-from-json functions will want to be able
to be called anywhere a single value returning function would be
normally called.  hstore/populate_record trick allows this, so it's
not apples to apples.

merlin