Thread: Semi-Pseudo Data Types & Procedure Arguments
Has anyone played around with what I would call "Semi-Pseudo Data Types," in which a stored procedure may accept a sub-set of a Pseudo Data Types but not just any pseudo data-type, such as any type of string (text, character varying, character), any type of integer (smallint, integer, bigint), or a single element or an array of elements?
The goal would be to define a function in such a way (or perhaps a custom data-type) which constrains to a list of existing data-types. If a custom data-type, then something like "anystring" or "anyint", similar to how the "any", "anyarray" and "anynonarray" pseudo-types already exist.
Here are a few example procedure calls which I'm curious about whether or not they could actually be implemented and if so, efficiently:
Example #1:
-- A stored procedure which can accept two arguments, each of which could be text, character varying, character varying(any length) or character(any length).
SELECT * FROM my_fn('val1'::text, 'val2'::character(4));
Example #2:
-- A stored procedure which can accept two arguments, each of which could be smallint, integer, or bigint.
SELECT * FROM my_fn('1'::smallint, '3726323626326262362'::bigint);
Example #3:
-- A stored procedure which can accept two argument, which can be a single integer field, or an array of integers.
SELECT * FROM my_fn(123::integer);
-- Or ...
SELECT * FROM my_fn('{123,456,789}'::integer[])
I have a few ideas in regards to how to accomplish Example #3 without the use of custom data-types, such as setting the procedure's argument to the "any" pseudo data-type and implementing in-procedure logic to reconcile whether the argument is an array or single element, and act accordingly such as throwing the single element into array, to logic wouldn't have to be duplicated.
In regards to Examples 1 & 2, I'm at a complete loss.
Any thoughts or suggestions are greatly appreciated!
Josh Burns
The goal would be to define a function in such a way (or perhaps a custom data-type) which constrains to a list of existing data-types. If a custom data-type, then something like "anystring" or "anyint", similar to how the "any", "anyarray" and "anynonarray" pseudo-types already exist.
Here are a few example procedure calls which I'm curious about whether or not they could actually be implemented and if so, efficiently:
Example #1:
-- A stored procedure which can accept two arguments, each of which could be text, character varying, character varying(any length) or character(any length).
SELECT * FROM my_fn('val1'::text, 'val2'::character(4));
Example #2:
-- A stored procedure which can accept two arguments, each of which could be smallint, integer, or bigint.
SELECT * FROM my_fn('1'::smallint, '3726323626326262362'::bigint);
Example #3:
-- A stored procedure which can accept two argument, which can be a single integer field, or an array of integers.
SELECT * FROM my_fn(123::integer);
-- Or ...
SELECT * FROM my_fn('{123,456,789}'::integer[])
I have a few ideas in regards to how to accomplish Example #3 without the use of custom data-types, such as setting the procedure's argument to the "any" pseudo data-type and implementing in-procedure logic to reconcile whether the argument is an array or single element, and act accordingly such as throwing the single element into array, to logic wouldn't have to be duplicated.
In regards to Examples 1 & 2, I'm at a complete loss.
Any thoughts or suggestions are greatly appreciated!
Josh Burns
On Wed, Jun 26, 2013 at 01:50:46AM -0400, Joshua Burns wrote:
> Example #1:
> -- A stored procedure which can accept two arguments, each of which could
> be text, character varying, character varying(any length) or character(any
> length).
> SELECT * FROM my_fn('val1'::text, 'val2'::character(4));
>
> Example #2:
> -- A stored procedure which can accept two arguments, each of which could
> be smallint, integer, or bigint.
> SELECT * FROM my_fn('1'::smallint, '3726323626326262362'::bigint);
...
> In regards to Examples 1 & 2, I'm at a complete loss.
I suppose you can use function overloading.
Have only the "base" function (that which operates on the
"largest" type, say TEXT/BIGINT) contain the actual code and
write wrappers in the SQL PL (not plpgsql) which accept
other types and cast towards TEXT and BIGINT as needed.
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Joshua Burns <jdburnz@gmail.com> writes:
> Has anyone played around with what I would call "Semi-Pseudo Data Types,"
> in which a stored procedure may accept a sub-set of a Pseudo Data Types but
> not just any pseudo data-type, such as any type of string (text, character
> varying, character), any type of integer (smallint, integer, bigint), or a
> single element or an array of elements?
Well, you can already handle such scenarios, no? Just accept text, or
bigint, and let the existing implicit conversions handle the other
cases.
> -- A stored procedure which can accept two argument, which can be a single
> integer field, or an array of integers.
Those two cases seem unlikely to be supportable by the same
implementation, so it seems more likely that what you'd be doing is just
overloading the function name with two instances, my_fn(int) and
my_fn(int[]).
Another trick that's often used is to use an implementation that isn't
quite as general as the signature might suggest. For example consider
create function my_add(anyelement, anyelement) returns anyelement as
'select $1 + $2' language sql;
This will work for any data type that has a "+" operator, which is a
smaller scope than the "anyelement" signature implies. Of course this
particular function is pretty useless compared to just writing "+", but
perhaps the idea will help you.
Anyway, I can't see much value in inventing "anystring" or "anyint".
The former is not distinguishable from "text" at all. The latter might
have some micro-efficiency gain compared to using "bigint", but probably
not enough to be worth the trouble.
regards, tom lane
Tom Lane-2 wrote
>
>> -- A stored procedure which can accept two argument, which can be a
>> single
>> integer field, or an array of integers.
>
> Those two cases seem unlikely to be supportable by the same
> implementation, so it seems more likely that what you'd be doing is just
> overloading the function name with two instances, my_fn(int) and
> my_fn(int[]).
Isn't this scenario why VARIDIC was implemented:
CREATE FUNCTION my_fn( VARIADIC in_ordered_actual varchar[] ) ....
The single value input is simply a special case of an array of size 1.
Depending on whether you allow an empty array you might want to:
CREATE FUNCTION my_fn(required_first varchar, VARIADIC optional_others
varchar[] DEFAULT '{}'::varchar[]) ...
Then join the two values together and move on to processing.
You would still need separate functions for numbers versus strings.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Semi-Pseudo-Data-Types-Procedure-Arguments-tp5761158p5761173.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David Johnston wrote
>
> Tom Lane-2 wrote
>>
>>> -- A stored procedure which can accept two argument, which can be a
>>> single
>>> integer field, or an array of integers.
>>
>> Those two cases seem unlikely to be supportable by the same
>> implementation, so it seems more likely that what you'd be doing is just
>> overloading the function name with two instances, my_fn(int) and
>> my_fn(int[]).
> Isn't this scenario why VARIDIC was implemented:
>
> CREATE FUNCTION my_fn( VARIADIC in_ordered_actual varchar[] ) ....
>
> The single value input is simply a special case of an array of size 1.
> Depending on whether you allow an empty array you might want to:
>
> CREATE FUNCTION my_fn(required_first varchar, VARIADIC optional_others
> varchar[] DEFAULT '{}'::varchar[]) ...
>
> Then join the two values together and move on to processing.
>
> You would still need separate functions for numbers versus strings.
>
> David J.
So my thoughts stand for generating ideas but you cannot actually supply an
array as an input to a VARIDIC routine; it simply allows for an unknown
number of inputs (of the same type) and constructs an array internally for
the function to use.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Semi-Pseudo-Data-Types-Procedure-Arguments-tp5761158p5761175.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.