Re: plpgsql - additional extra checks

2017-01-13 2:46 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 1/11/17 5:54 AM, Pavel Stehule wrote:
>
>> + <term><varname>too_many_rows</varname></term>
>> + <listitem>
>> + <para>
>> + When result is assigned to a variable by <literal>INTO</literal>
>> clause,
>> + checks if query returns more than one row. In this case the
>> assignment
>> + is not deterministic usually - and it can be signal some issues in
>> design.
>>
>
> Shouldn't this also apply to
>
> var := blah FROM some_table WHERE ...;
>

declare x int;
begin
x := i from generate_series(1,1) g(i);
raise notice 'x=%', x;
end;
$$;
NOTICE: x=1
DO

postgres=# do $$
declare x int;
begin
x := i from generate_series(1,2) g(i);
raise notice 'x=%', x;
end;
$$;
ERROR: query "SELECT i from generate_series(1,2) g(i)" returned more than
one row
CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment

so extra check is not required in this case

>
> ?
>
> AIUI that's one of the beefs the plpgsql2 project has.
>

uff - I hope so plpgsql2 will carry some less scary - against the clean
syntax

x := (select .. )

you save 8 chars. And now the SELECT doesn't look like SELECT - the
statement was broken. This feature is just side effect of plpgsql quick (in
old time little bit poor) design. It is not allowed in PL/SQL and it is not
allowed by SQL/PSM.

>
> FWIW, I'd also be in favor of a NOMULTI option to INTO, but I don't see
> any way to do something like that with var := blah FROM.

This is proposed as check for current living code, where you should not to
modify source code.

We can speak about introduce new keyword or new syntax - but it should be
different thread.

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