以下postgresql存储过程的目的是什么？

What's the purpose of the following postgresql stored procedure, it's used when execute a query, thank you!

以下postgresql存储过程的目的是什么,它在执行查询时使用,谢谢!

CREATE OR REPLACE FUNCTION extract_fp_query(int[]) RETURNS int[]
AS $$
    SELECT uniq(sort(subarray($1 - 627964279,
        greatest(0, least(icount($1 - 627964279) - 120, 80)), 120)));
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

1 个解决方案

#1

This code relies heavily on functionality in the intarray extension. Once you know that you can work it out by breaking it down into steps.

此代码在很大程度上依赖于intarray扩展中的功能。一旦你知道你可以通过分解成步骤来解决它。

It looks to me like some kind of poor-man's crypto or obfuscation routine, but you've failed to provide any information about its inputs so it's hard to say more. Essentially it returns a sorted, de-duplicated subset of the passed array, deciding which subset to return based on the number of elements in the array. If I read it correctly it removes the value 627964279 from the array if it appears there, then returns 120 elements (before de-duplication) from an offset of 0 and 80 elements into the array depending on the number of elements in the array.

它看起来像某种穷人的加密或混淆程序,但你没有提供任何有关其输入的信息,所以很难说更多。本质上,它返回传递数组的已排序,重复数据删除的子集,根据数组中的元素数决定返回哪个子集。如果我正确读取它会从数组中删除值627964279(如果它出现在那里),然后根据数组中元素的数量,将0和80个元素的偏移量返回120个元素(在重复数据删除之前)。

Create a test database. I'll call mine regress. Create it and install the intarray contrib module into it as a superuser:

创建一个测试数据库。我会称之为退步。创建它并以超级用户身份将intarray contrib模块安装到其中:

sudo -u postgres createdb -O myusername regress
sudo -u postgres psql regress -c 'CREATE EXTENSION intarray;'

Now running as myusername, whatever your unpriveleged user account is, break the code down into steps and try each step. Just like you would in C. It might help to format it into expressions:

现在作为myusername运行,无论您的未充分利用的用户帐户是什么,请将代码分解为步骤并尝试每个步骤。就像在C中一样。将其格式化为表达式可能会有所帮助:

SELECT uniq(sort(
    subarray(
        $1 - 627964279,
        greatest(
            0, 
            least(
                icount($1 - 627964279) - 120,
                80
            )
        )
        , 120
    )
));

then evaluate each sub-expression by hand with a known input, substituting results as you go and simplifying the expression while jotting down what it does. I can't do it for you because you haven't supplied a sample input, but, replacing ARRAY[42,5,9,24,1,627964279] with your input array you'd do something like:

然后用一个已知的输入手动评估每个子表达式,随意替换结果并简化表达式,同时记下它的作用。我无法为你做这个,因为你还没有提供样本输入,但用您的输入数组替换ARRAY [42,5,9,24,1,627964279],你会做类似的事情:

$ psql regress
psql (9.2.1)
Type "help" for help.

regress=> SELECT ARRAY[42,5,9,24,1,627964279];
          array          
-------------------------
 {42,5,9,24,1,627964279}
(1 row)

regress=> SELECT ARRAY[42,5,9,24,1,627964279] - 627964279;
   ?column?    
---------------
 {42,5,9,24,1}
(1 row)

regress=> SELECT icount(ARRAY[42,5,9,24,1,627964279] - 627964279);
 icount 
--------
      5
(1 row)

regress=> SELECT least(icount(ARRAY[42,5,9,24,1,627964279] - 627964279),80);
 least 
-------
     5
(1 row)

regress=> 
regress=> SELECT greatest(least(icount(ARRAY[42,5,9,24,1,627964279] - 627964279),80),0);
 greatest 
----------
        5
(1 row)

Now, by substituting 5 for the expression greatest(...) into the subarray expression we get:

现在,通过将5表示最大的表达式(...)替换为子数组表达式,我们得到:

SELECT subarray($1 - 627964279, 5, 120 )

which is after the evaluation of the array item removal:

这是在数组项目删除评估之后:

regress=> SELECT subarray(ARRAY[42,5,9,24,1], 5, 120);
 subarray 
----------
 {1}
(1 row)

In this case the sort and uniq have no further effect.

在这种情况下,sort和uniq没有进一步的效果。

What's it for? Who knows, as you haven't provided an input array that might offer clues.

这是为了什么?谁知道,因为你没有提供可能提供线索的输入数组。

See the intarray documentation.

请参阅intarray文档。

#1