Semi-Pseudo Data Types & Procedure Arguments

From: Joshua Burns <jdburnz(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Semi-Pseudo Data Types & Procedure Arguments
Date: 2013-06-26 05:50:46
Message-ID: CAB73KvQBz8RiQxYec6weZTXnyLBuM-Dm4ffyxH-uYAdApNGLdA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adarsh Sharma 2013-06-26 05:56:30 Re: Concatenate table name in Pl/Pgsql
Previous Message Ian Lawrence Barwick 2013-06-26 05:44:50 Re: Concatenate table name in Pl/Pgsql