On Fri, May 29, 2020 at 8:30 PM Pavel Stehule <pavel.steh...@gmail.com> wrote:
> > > pá 29. 5. 2020 v 16:45 odesílatel Prabhat Sahu < > prabhat.s...@enterprisedb.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
