How to convert input parameter type in postgresql function

Question

Can I declare a function in postgresql which in its input parameter will convert data type of passed parameter. For example:

create or replace function temp(IN var1 integer, IN var2 varchar)
<function body>

select temp('12345','number');

as passed parameter '12345' is a string, I want it to convert directly into integer while passing it to function, something like this:

create or replace function temp(IN var1 ::integer, IN var2 varchar)
    <function body>

Is it possible?

Note: I cannot convert value while passing it to the function like this:

select temp('12345'::integer,'number');

I have to convert it in function definition itself.

Laurenz Albe · Accepted Answer · 2018-03-01 11:26:27Z

2

You could create a cast from text to integer, but that's a bad idea as it will lead to surprising behavior somewhere else.

You have two choices:

Cast the argument to integer before passing it to the function.
Cast the argument to integer inside the function.

answered Mar 1, 2018 at 11:26

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Vishal D Over a year ago

So, this means I can not perform conversion in function definition itself and I have do it explicitly inside the function?

Laurenz Albe Over a year ago

Right. Type conversion while passing the argument would be an assignment or implicit cast, and I recommend that you do not go that way.

McNets · Accepted Answer · 2018-03-01 11:28:35Z

0

CREATE OR REPLACE FUNCTION totalRecords (P1 int, P2 int)
RETURNS integer AS $total$
declare
  total integer;
BEGIN
   total := P1 * P2;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

✓

select totalRecords('12', 3)

| totalrecords |
| -----------: |
|           36 |

dbfiddle here

You could use CREATE CAST if you need special data type.

CREATE CAST defines a new cast. A cast specifies how to perform a conversion between two data types.

CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name (argument_type [, ...])
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]

edited Mar 1, 2018 at 11:28

answered Mar 1, 2018 at 11:23

McNets

10.9k3 gold badges39 silver badges70 bronze badges

1 Comment

Vishal D Over a year ago

I cannot convert it while calling the function, see Note in question.

Collectives™ on Stack Overflow

How to convert input parameter type in postgresql function

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related