Re: PG function with pseudotype "anyelement" for IN, OUT parameter shows wrong behaviour.

On Fri, May 29, 2020 at 8:30 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> pá 29. 5. 2020 v 16:45 odesílatel Prabhat Sahu <
> prabhat(dot)sahu(at)enterprisedb(dot)com> napsal:
>
>> Hi All,
>>
>> Please check the below scenario, with pseudotype "anyelement" for IN, OUT
>> parameter and the RETURN record in a function.
>>
>> postgres=# create table tab1(c1 int, c2 int, c3 timestamp) ;
>> CREATE TABLE
>> postgres=# CREATE OR REPLACE FUNCTION func_any(IN anyelement, IN
>> anyelement, OUT v1 anyelement, OUT v2 anyelement)
>> RETURNS record
>> AS
>> $$
>> BEGIN
>> SELECT $1 + 1, $2 + 1 into v1, v2;
>> insert into tab1 values(v1, v2, now());
>> END;
>> $$
>> language 'plpgsql';
>> CREATE FUNCTION
>> postgres=# SELECT (func_any(1, 2)).*;
>> v1 | v2
>> ----+----
>> 2 | 3
>> (1 row)
>>
>> postgres=# select * from tab1;
>> c1 | c2 | c3
>> ----+----+----------------------------
>> 2 | 3 | 2020-05-30 19:26:32.036924
>> 2 | 3 | 2020-05-30 19:26:32.036924
>> (2 rows)
>>
>> I hope, the table "tab1" should have only a single record, but we are
>> able to see 2 records in tab1.
>>
>
> it is correct, because you use composite unpacking syntax
>
> SELECT (func_any(1, 2)).*;
>
> means
>
> SELECT (func_any(1, 2)).c1, (func_any(1, 2)).c2;
>
> If you don't want double execution, you should to run your function in
> FROM clause
>
> postgres=# SELECT * FROM func_any(1, 2);
> ┌────┬────┐
> │ v1 │ v2 │
> ╞════╪════╡
> │ 2 │ 3 │
> └────┴────┘
> (1 row)
>

Thanks Pavel, for the help, I have verified the same, Now I am getting a
single record in tab1.
postgres=# SELECT func_any(1, 2);
func_any
----------
(2,3)
(1 row)

postgres=# select * from tab1;
c1 | c2 | c3
----+----+----------------------------
2 | 3 | 2020-05-30 20:17:59.989087
(1 row)
Thanks,
Prabhat Sahu