Re: PG function with pseudotype "anyelement" for IN, OUT parametershows wrong behaviour. - Mailing list pgsql-hackers

From Prabhat Sahu
Subject Re: PG function with pseudotype "anyelement" for IN, OUT parametershows wrong behaviour.
Date
Msg-id CANEvxPq8KErsXzxJ2eUrzBotKeVHkP=4vRnJXwto2rp9fK+joA@mail.gmail.com
Whole thread Raw
In response to Re: PG function with pseudotype "anyelement" for IN, OUT parametershows wrong behaviour.  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers


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


pá 29. 5. 2020 v 16:45 odesílatel Prabhat Sahu <prabhat.sahu@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

pgsql-hackers by date:

Previous
From: David Fetter
Date:
Subject: Re: proposal: possibility to read dumped table's name from file
Next
From: Tom Lane
Date:
Subject: Re: proposal: possibility to read dumped table's name from file