Postgresql equivalent for SQL Server function Convert

Question

I am in need of a Postgres function which is equivalent to SQL Server function convert. I ended up writing a function

    CREATE OR REPLACE FUNCTION convert (target VARCHAR(50),source ANYELEMENT,style INT) RETURNS ANYELEMENT
    AS
    $$ BEGIN
    CASE
   when style=0 then CASE
   WHEN target ='int' THEN return source::int;
   WHEN target ='smallint' or target = 'tinyint' THEN return source::smallint;
   WHEN target ='bigint' THEN return source::bigint;
   WHEN target ='numeric' or target = 'real' or target = 'float' THEN return source::double precision;
   WHEN target ='smallmoney' or target = 'money' or target = 'decimal' THEN return source::double precision;
   WHEN target ='char' THEN return source::char;
   WHEN target ='datetime' or target = 'smalldatetime' then return source::timestamp;
   WHEN target like 'varchar%' or target like 'nvarchar%' THEN return source::varchar;
   WHEN target = 'text' or target like 'ntext' THEN return source::text;
   WHEN target = 'timestamp' THEN return source::varchar(30);
   WHEN target = 'binary' or target='varbinary' THEN return source::bytea;
   WHEN target = 'uniqueidentifier' THEN return source::varchar(37);
   WHEN target = 'sysname' THEN return source::varchar(128);
   WHEN target = 'sql_variant' THEN return varchar;
   WHEN target = 'bit' THEN if source::varchar='1' THEN return true; ELSIF source::varchar='0' THEN return false; else RAISE EXCEPTION 'Invalid value for Input boolean'; END IF;
   ELSE return source::anyelement;
END CASE;
   when style!=0 then CASE
   WHEN (style = 0 OR style = 100) THEN return to_timestamp(source::text,'mon dd yyyy hh:miAM');
       WHEN style = 1 THEN return to_timestamp(source::text,'mm/dd/yy');
       WHEN style = 101 THEN return to_timestamp(source::text,'mm/dd/yyyy');
       ..
   ..
END CASE;
 END CASE;
  END;
$$ LANGUAGE plpgsql;

While calling the function as select convert('int','1',0), it throws the following error:

"ERROR: could not determine polymorphic type because input has type "unknown" 1 statement failed."

When I do an explicit cast of second parameter as convert('int','1'::text,0), it works. But I need to call without doing any additional cast. Can someone help?

What exactly does MS-SQL Server's `convert` function do that you're trying to replicate, and why do you need it? — Craig Ringer, Apr 11 '17 at 07:39
Why not just use `CAST` with optionally `to_char`/`to_timestamp` when needed? -- What you want will never work on PostgreSQL, because the literal `'1'` has [unknown](https://www.postgresql.org/message-id/183.1302200970%40sss.pgh.pa.us) type. You always need to provide type information; either in the typed literal syntax `int '1'` or with casts `'1'::int` or `CAST('1' AS int)`. If you use prepared statements & provide type information via binding (which you should anyway), you don't have to use casts though. — pozs, Apr 11 '17 at 07:58

score 0 · Answer 1 · answered Apr 12 '17 at 03:52

You cannot to write function like convert without changes in PostgreSQL parser. Use PostgreSQL cast function instead. anyelement is like template - and if anyelement on input is int, then anyelement on output will be int too. This is not what you want.

PostgreSQL doesn't allow to write any function when the result type will be selected inside function. Result type should be known before you call the function. And because PostgreSQL known nothing about your function, then cannot to select result type like you need.

Postgresql equivalent for SQL Server function Convert

1 Answers1