时间戳

首页 > 解决方案 > port Oracle decode() using variadic, anyarray, and anyelement

postgresql - port Oracle decode() using variadic, anyarray, and anyelement

问题描述

I need to port from Oracle a stored procedure that uses decode() extensively. That is, I can't use series of CASE WHEN expr THEN expr [...] ELSE as the guide suggests.

I wanted to create a variadic function, but here's the problem: in Oracle the function can accept any number of key-value pairs and the type of the key not necessarily matches the type of the value:

select decode(0  ,0,'a'  ,1,'b'  ,2,'c'  /*,...*/ ,'dflt') from dual;

I tried to use anyarray:

create or replace function decode(VARIADIC args anyarray) RETURNS text AS $$
  SELECT null::text;
$$ LANGUAGE SQL;

But this only works when all the arguments are of the same type:

select decode(0,0,0); -- ok
select decode('x'::text,'x'::text,'x'::text); -- ok
select decode(0,0,'a'::text); -- No function matches the given name and argument types

If the desired syntax is not possible please advise another way of passing the expr, the set of pairs, and the default value while keeping their positions same as in Oracle.

标签： postgresqlpolymorphic-functions

解决方案

Limitation

It seems it is how PostgreSQL is implemented. Reading the docs:

35.4.5. SQL Functions with Variable Numbers of Arguments

SQL functions can be declared to accept variable numbers of arguments, so long as all the "optional" arguments are of the same data type. The optional arguments will be passed to the function as an array. The function is declared by marking the last parameter as VARIADIC; this parameter must be declared as being of an array type.

JSON

If you find a way to export your mixed array into JSON format from Oracle, then PostgreSQL JSON type will cope with it:

CREATE OR REPLACE FUNCTION xdecode(data json)
RETURNS TEXT AS
$BODY$
    -- Your implementation here
    SELECT NULL::TEXT;
$BODY$ LANGUAGE SQL;

This function accepts a JSON string and it can be like:

SELECT xdecode('[1, 2, 3.3, "a", true, null]'::json);

Table Type

If the tuple of arguments you want to decode matches a TABLE type, then you can use it:

CREATE TABLE foo(
    x INTEGER,
    y FLOAT,
    z TEXT
);

CREATE OR REPLACE FUNCTION xdecode2(data foo)
RETURNS TEXT AS
$BODY$
    SELECT row_to_json(data)::TEXT;
$BODY$ LANGUAGE SQL;

Then this call works:

SELECT xdecode2((1, 2.1, 'x'))

Unfortunately we cannot use the generic RECORD type as input argument for a function.

CREATE OR REPLACE FUNCTION xdecode3(data RECORD)
RETURNS TEXT AS
$BODY$
    SELECT row_to_json(data)::TEXT;
$BODY$ LANGUAGE SQL;

Raises:

ERROR: SQL functions cannot have arguments of type record
SQL state: 42P13

Type anyelement

As pointed out by @basin, type RECORD can be emulated using anyelement:

CREATE OR REPLACE FUNCTION xdecode4(data anyelement)
RETURNS TEXT AS
$BODY$
    SELECT row_to_json(data)::TEXT;
$BODY$ LANGUAGE SQL;

SELECT xdecode4((1, 2.1, 'x'));

Returns:

'{"f1":1,"f2":2.1,"f3":"x"}'

推荐阅读