MIME-Version: 1.0
From: Durumdara <durumdara@gmail.com>
Date: Wed, 5 Jun 2024 10:34:54 +0200
Message-ID: <CAEcMXhkoR7mo3gpF8Jhoq=ui6vgCC5rvKW3e2wF=PfzL=FWJAQ@mail.gmail.com>
Subject: Variant (Untyped) parameter for function/procedure
To: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000059a676061a2071ef"
Archived-At: <https://www.postgresql.org/message-id/CAEcMXhkoR7mo3gpF8Jhoq%3Dui6vgCC5rvKW3e2wF%3DPfzL%3DFWJAQ%40mail.gmail.com>
Precedence: bulk

--00000000000059a676061a2071ef
Content-Type: text/plain; charset="UTF-8"

Dear Members!

As I experienced, the functions/procedures extremely depend on parameters
(very typed).
So if I have to restructure the input parameters, I can't modify the
function, because I have to recreate the dependents too.
For example:
I have a type. If I pass this type to a function, I can't change the
structure of the type without dropping and recreating the function.

create type blahtype as (a int, b bool);
create function blahcheck (input blahtype) ...


If I have many dependent functions this causes that I have to drop
(recreate) everything - just for an extra parameter. And sometimes this
extra parameter doesn't change 10 functions, only one. But I have to
recreate them all (without changing the body).

Is there any way to use a Variant parameter?
Like this:

create procedure test(IN Input Record, OUT Output Record)
...
    Time = Input.Time::timestamp;
...

    Output = SomeHowMakeItTyped;

    Output.Result = 1;

...


Caller:

...
for r_in as select  id, name from blah into
...
     test(r_in, r_out);
     if r_out.result <> 0 then ...

Or:

create procedure test(IN Inputs Records, OUT Output Record)
...
        for Input in Inputs:
        Time = Input.Time::timestamp;
...
    Output.Result = 1;
...


Or is it impossible, because of the PGSQL's philosophy (very typed)?

If it is not possible then I have one way I think.
It is a JSON data type for inputs and outputs.

Is that right?

Thanks for your any help, info!

Best regards
dd

--00000000000059a676061a2071ef
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Dear Members!<div><br></div><div>As I experienced, the fun=
ctions/procedures extremely depend on parameters (very typed).</div><div>So=
 if I have to restructure the input parameters, I can&#39;t modify the func=
tion, because I have to recreate the dependents too.</div><div>For=C2=A0exa=
mple:</div><div>I have a type. If I pass this type to a function, I can&#39=
;t change the structure of the type without dropping and recreating the fun=
ction.</div><div><br></div><div><blockquote style=3D"margin:0 0 0 40px;bord=
er:none;padding:0px"><font face=3D"monospace">create type blahtype as (a in=
t, b bool);<br>create function blahcheck (input blahtype) ...</font></block=
quote></div><div><br></div><div>If I have many dependent functions this cau=
ses that=C2=A0I have to drop (recreate) everything - just for an extra para=
meter. And sometimes this extra parameter doesn&#39;t change 10 functions, =
only one. But I have to recreate=C2=A0them all (without changing the body).=
</div><div><br></div><div>Is there any way to use a Variant parameter? <br>=
Like this:</div><blockquote style=3D"margin:0 0 0 40px;border:none;padding:=
0px"><div><font face=3D"monospace">create procedure test(IN Input Record, O=
UT Output Record)=C2=A0</font></div><div><font face=3D"monospace">...</font=
></div><div><font face=3D"monospace">=C2=A0 =C2=A0 Time =3D Input.Time::tim=
estamp;</font></div><div><font face=3D"monospace">...</font></div></blockqu=
ote><blockquote style=3D"margin:0 0 0 40px;border:none;padding:0px"><div><f=
ont face=3D"monospace">=C2=A0 =C2=A0 Output =3D SomeHowMakeItTyped;</font><=
/div></blockquote><blockquote style=3D"margin:0 0 0 40px;border:none;paddin=
g:0px"><div><font face=3D"monospace">=C2=A0 =C2=A0 Output.Result =3D 1;</fo=
nt></div></blockquote><blockquote style=3D"margin:0 0 0 40px;border:none;pa=
dding:0px"><div><font face=3D"monospace">...</font></div></blockquote><div>=
<br>Caller:<br></div><blockquote style=3D"margin:0 0 0 40px;border:none;pad=
ding:0px"><div><font face=3D"monospace">...</font></div><div><font face=3D"=
monospace">for r_in as select=C2=A0 id, name from blah into</font></div><di=
v><font face=3D"monospace">...</font></div><div><font face=3D"monospace">=
=C2=A0 =C2=A0 =C2=A0test(r_in, r_out);</font></div><div><font face=3D"monos=
pace">=C2=A0 =C2=A0 =C2=A0if r_out.result &lt;&gt; 0 then ...</font></div><=
/blockquote><div>Or:</div><div><blockquote style=3D"margin:0 0 0 40px;borde=
r:none;padding:0px"><span style=3D"font-family:monospace">create procedure =
test(IN Inputs Records, OUT Output Record)=C2=A0</span><br><span style=3D"f=
ont-family:monospace">...<br></span>=C2=A0 =C2=A0 =C2=A0 =C2=A0 for Input i=
n Inputs:<br><span style=3D"font-family:monospace">=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 Time =3D Input.Time::timestamp;</span><br><div><font face=3D"monospace"=
>...</font></div><div><font face=3D"monospace">=C2=A0 =C2=A0 Output.Result =
=3D 1;</font></div><div><font face=3D"monospace">...<br></font></div></bloc=
kquote><br><blockquote style=3D"margin:0 0 0 40px;border:none;padding:0px">=
<br class=3D"gmail-Apple-interchange-newline"></blockquote></div><div><br>O=
r is it impossible, because of the PGSQL&#39;s=C2=A0philosophy (very typed)=
?</div><div><br></div><div>If it is not possible then I have one way I thin=
k.=C2=A0</div><div>It is a JSON=C2=A0data type for inputs and outputs.</div=
><div><br></div><div>Is that right?</div><div><br></div><div>Thanks for you=
r any help, info!</div><div><br></div><div>Best regards</div><div>dd</div><=
div><br><br></div><div><br></div><div><br></div><div><br></div></div>

--00000000000059a676061a2071ef--