Re: PG function with pseudotype "anyelement" for IN, OUT parameter shows wrong behaviour.
On Fri, May 29, 2020 at 8:30 PM Pavel Stehule 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
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.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) Regards Pavel > -- > > With Regards, > Prabhat Kumar Sahu > EnterpriseDB: http://www.enterprisedb.com >
PG function with pseudotype "anyelement" for IN, OUT parameter shows wrong behaviour.
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. -- With Regards, Prabhat Kumar Sahu EnterpriseDB: http://www.enterprisedb.com
