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

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)

Regards

Pavel

> --
>
> With Regards,
> Prabhat Kumar Sahu
> EnterpriseDB: http://www.enterprisedb.com
>