Re: BUG #14549: pl/pgsql parser

2017-02-17 10:44 GMT+01:00 Wei Congrui <crvv(dot)mail(at)gmail(dot)com>:

> Hello,
>
> In the document, https://www.postgresql.org/docs/9.6/
> static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
> "If a row or a variable list is used as target, the query's result
> columns must exactly match the structure of the target as to
> number and data types, or else a run-time error occurs. When
> a record variable is the target, it automatically configures itself
> to the row type of the query result columns."
>
>
> I think this is a bug according to the document.
>

yes, it is not valid

Pavel

>
>
> Regards,
> Wei Congrui
>
> 2017-02-17 17:19 GMT+08:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>> Hi
>>
>> 2017-02-17 8:58 GMT+01:00 <stefanov(dot)sm(at)abv(dot)bg>:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 14549
>>> Logged by: Stefan Stefanov
>>> Email address: stefanov(dot)sm(at)abv(dot)bg
>>> PostgreSQL version: 9.5.3
>>> Operating system: Red Hat, 64 bit
>>> Description:
>>>
>>> Hi all,
>>> I found (the hard way) that in pl/pgsql SELECT INTO statement a syntax
>>> error
>>> may remain unnoticed.
>>> This simple example works as expected and produces '1, 2, 3' notice.
>>>
>>> DO language plpgsql
>>> $$
>>> DECLARE
>>> vara integer;
>>> varb integer;
>>> varc integer;
>>> BEGIN
>>> SELECT 1, 2, 3 INTO vara, varb, varc;
>>> RAISE NOTICE '% % %', vara, varb, varc;
>>> END;
>>> $$;
>>>
>>> However if you omit a comma (or even replace the comma with AS) between
>>> varb
>>> and varc in the INTO list then no syntax error is produced and the
>>> resulting
>>> notice is '1 2 <NULL>'.
>>>
>>> DO language plpgsql
>>> $$
>>> DECLARE
>>> vara integer;
>>> varb integer;
>>> varc integer;
>>> BEGIN
>>> SELECT 1, 2, 3 INTO vara, varb AS varc;
>>> RAISE NOTICE '% % %', vara, varb, varc;
>>> END;
>>> $$;
>>>
>>> A few more clearly erratic combinations of SELECT expressions and the
>>> INTO
>>> list also 'work' and issue misleading results.
>>> Same in functions. For me it produced a bug that was difficult to see and
>>> track.
>>>
>>> Best,
>>> Stefan
>>>
>>>
>> It is not a bug - plpgsql is designed be tolerant to different columns
>> and data types in left and right part of assignment.
>>
>> You can use some tools for easy detecting these issues:
>>
>> 1. plpgsql_check https://github.com/okbob/plpgsql_check - it is
>> available in community repository
>> 2. prepared extra_checks https://commitfest.postgresql.org/13/962/
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>>
>>>
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>>>
>>
>>
>