Can the OUT parameter be enabled in stored procedure?

From: David Zhang <david(dot)zhang(at)highgo(dot)ca>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Can the OUT parameter be enabled in stored procedure?
Date: 2020-04-29 23:42:50
Message-ID: b926e4a3-11a2-554a-1f29-406568169d5e@highgo.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

I found two email threads below,

https://www.postgresql.org/message-id/b0d099ca-f9c3-00ed-0c95-4d7a9f7c97fc%402ndquadrant.com

https://www.postgresql.org/message-id/CA%2B4BxBwBHmDkSpgvnfG_Ps1SEeYhDRuLpr1AvdbUwFh-otTg8A%40mail.gmail.com

and I understood "OUT parameters in procedures are not implemented yet,
but would like to have
in the future" at that moment. However, I ran a quick test by simply
commented out a few lines below in src/backend/commands/functioncmds.c

+//             if (objtype == OBJECT_PROCEDURE)
+//             {
+//                     if (fp->mode == FUNC_PARAM_OUT)
+//                             ereport(ERROR,
+// (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+// errmsg("procedures cannot have OUT arguments"),
+//                                              errhint("INOUT
arguments are permitted.")));
+//             }

then I can use OUT as the parameter to create a PROCEDURE, and I can do
something like,

postgres=# create procedure p2(IN a int, OUT b int) as 'select 9*$1'
language sql;
CREATE PROCEDURE
postgres=# CALL p2(1);
 b
---
 9
(1 row)

By enabling the OUT parameter, I can see some difference, for example,
1. user doesn't have to provide one (or many) dummy "INOUT" parameter in
order to get the output
2. it has similar behavior compare with FUNCTION when using IN, INOUT,
and OUT parameters

So, the questions are,

1. what are the limitation or concern that block the support of the OUT
parameter at this moment?

2. if the OUT parameter is enabled like above, what will be the impact?

3. Is there any other components that should be considered following the
above change?

Thanks,
--
David

Software Engineer
Highgo Software Inc. (Canada)
www.highgo.ca

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Kimura 2020-04-29 23:44:53 Re: Avoiding hash join batch explosions with extreme skew and weird stats
Previous Message Jonathan S. Katz 2020-04-29 23:40:25 Re: Poll: are people okay with function/operator table redesign?