Re: merging some features from plpgsql2 project

2017-01-08 3:39 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 1/7/17 2:06 AM, Pavel Stehule wrote:
>
>>
>> SELECT t1 := c1, t2 := c2, ...
>>
>> - it can be PostgreSQL specific syntax - full control over design
>> - maximally robust against typo
>> - long syntax, but for short syntax can be used SELECT c1,c2,c3, .. INTO
>> STRICT recvar
>>
>
> I don't think overloading a SQL command is a good idea. We'd be in trouble
> if ANSI ever introduced :=. I think that could also conflict with existing
> operators.

The ":=" operator is used ANSI/SQL already for named arguments. Isn't
probable so ANSI uses it in different context.

This is not overloading of SQL command - it is like annotations. It is
smart idea, so I was not surprised if ANSI/SQL reuses it. There is not any
possible construct, that is robust against typo - because assignment is
very verbose and natural.

ANSI - SQL/PSM uses two methods

1. multiassignment

SET (a,b,c) = (SELECT a, b, c ...)

2. auto variables in dedicated new scope

FOR scope_label IN SELECT a, b, c
DO
-- you can use variables a, b, c
-- you can use qualified variables scope_label.a, scope_label.b, ..
END FOR

This method is not possible in PL/pgSQL - but a work with record type is
similar

>
>
> - what should be no_data_found behave?
>>
>
> Considering where we're at today, I don't think there should be a default
> behavior; make the user specify somehow whether missing data is allowed or
> not.
>
> I have nothing about a cost of "new syntax" implementation - but for me
>> - it looks like good solution for us - it can be win/win solution. It
>> breaks nothing - it introduce nice to have typo robust syntax.
>>
>
> Related to that, I suspect we could add better support to existing
> commands for at least some of these things. For example, SELECT ... INTO
> NOMULTI (instead of STRICT) to indicate that multiple rows are an error but
> missing data is

Another flag into NOMULTI can be solution too.

The new syntax ":=" has some advantages:

1. it robust against type - it is harder to do unwanted swap of variables,
and this mistake is very clear
2. the syntax ensure equality of target variables and source expressions.

I see valuable benefit of this syntax

Regards

Pavel

> OK.
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)
>