Feedback-ID: i76984098:Fastmail
Message-ID: <e5e59360-4852-4854-a6e3-31adb4eb3a2e@aklaver.com>
Date: Wed, 5 Jun 2024 08:01:30 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Variant (Untyped) parameter for function/procedure
To: Durumdara <durumdara@gmail.com>,
 Postgres General <pgsql-general@postgresql.org>
References: <CAEcMXhkoR7mo3gpF8Jhoq=ui6vgCC5rvKW3e2wF=PfzL=FWJAQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEcMXhkoR7mo3gpF8Jhoq=ui6vgCC5rvKW3e2wF=PfzL=FWJAQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/e5e59360-4852-4854-a6e3-31adb4eb3a2e%40aklaver.com>
Precedence: bulk

On 6/5/24 01:34, Durumdara wrote:
> 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?

You mean like:

https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC

Examples here:

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS

Starting at:

"... When the return type of a PL/pgSQL function is declared as a 
polymorphic type ..."

Or there is VARIADIC:

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS


> 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
> 
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com