Is it allowed to write a SQL function in PostgreSQL to return more then one data type?

Question

I am working on a compatibility issue, I want to create a SQL function which can return an int or a Varchar based on the conditions in the program.

For example, I have a function named foo.

CREATE or REPLACE FUNCTION foo(param VARCHAR(100))
-- Which datatype I should use here?
RETURNS SOME_DATATYPE AS
$$
  SELECT
    CASE UPPER(param)
        WHEN 'varchar'  THEN CAST((SELECT 'varchar_value') AS varchar)
        WHEN 'int'      THEN CAST((SELECT 1426598) AS int)
        ELSE param||' is not supported.'
        END
$$
LANGUAGE SQL;

For the following queries, I am expecting output as follows.

select pg_typeof(foo('varchar')) from dual;
--I am expecting varchar as output.

select pg_typeof(foo('int')) from dual;
--I am expecting int as output.

Please suggest if such a feature is there, Or any alternative I can try to achieve the same.

A CASE expression returns a single value and all WHEN parts of such an expression must return the same data type, so that part will already not work. But "can return an int or a Varchar based on the conditions in the program" is also not possible. — user330315
– user330315, Commented Aug 7, 2020 at 8:11
Unrelated, but: CAST((SELECT 1426598) AS int) can be simplified to CAST(1426598 AS int) the SELECT is completely useless — user330315
– user330315, Commented Aug 7, 2020 at 8:11

user330315 · Accepted Answer · 2020-08-07 09:40:50Z

2

The closest thing would be to overload the function:

create or replace function foo(param text)
  returns text
$$
select param;
$$
language sql;

create or replace function foo(param int)
  returns int
$$
select param;
$$
language sql;

create or replace function foo(param date)
  returns int
$$
select param;
$$
language sql;

answered Aug 7, 2020 at 9:40

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is it allowed to write a SQL function in PostgreSQL to return more then one data type?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related