如何将参数传递给日期函数 [英] How to pass a parameter into a date function

问题描述

我正在尝试创建一个简单的函数，但似乎无法将参数传递给date函数.
功能如下:

I am trying to create a simple function and I cannot seem to pass in a parameter into the date function.
Here is the function:

CREATE OR REPLACE FUNCTION test(source int,days text)
RETURNS integer AS $totals$
declare
    totals integer;
BEGIN

   select 
           count(id) into totals
     from  ad
    where
       createdate::date = date(current_date - interval '$2' day) and
       source = $1;

   RETURN totals;

END;
$totals$ LANGUAGE plpgsql;

推荐答案

@IMSoP 已经阐明了您的语法错误.但是，可以通过多种方式更简单，更快和更清洁.

@IMSoP already shed light upon your syntax error. However, this can be simpler, faster and cleaner in multiple ways.

CREATE OR REPLACE FUNCTION test(_source int, _days int)
  RETURNS integer AS
$func$
SELECT count(*)::int
FROM   ad a
WHERE  a.source = $1
AND    a.createdate::date = current_date - $2
$func$  LANGUAGE sql STABLE;

• 首先，要从date中减去天数，您可以减去一个integer 数字.因此，我在这里使用integer参数.

  • First of all, to subtract days from a date, you can can just subtract an integer number. Accordingly I use an integer parameter here.

    • 如何使用PostgreSQL确定上个月的最后一天?

    对于这样的简单函数，您不需要plpgsql.请使用 SQL函数-在较大的查询上下文中可以内联"该函数，因此在某些情况下可以进行更好的优化.

    You don't need plpgsql for a simple function like this. Use an SQL function instead - which can be "inlined" in the context of a bigger query, and thus optimized better in certain cases.

    该功能可以为 STABLE :

    The function can be STABLE:

    • 如何进行IMMUTABLE ，STABLE和VOLATILE关键字会影响功能的行为吗?

    您的功能中内置了命名冲突. source显然也是一个列名.尽量避免这种情况.常见的做法是在变量和参数前加上下划线(否则没有特殊含义).您还可以使用函数名(或使用位置参数)对列名和/或参数名进行表限定，以使它们明确.我都是在这里做的.

    You had a naming conflict built into your function. source is obviously also a column name. Try to avoid that. A common practice is to prepend variables and parameters with an underscore (which has no special meaning otherwise). You can also table-qualify column names and/or prepend parameter names with the function name (or use positional parameters) to be unambiguous. I did both here.

    假设id是您的PK列，并因此定义了NOT NULL，count(*)的作用与count(id)相同，但更短，更便宜.我转换为integer，因为count()将返回bigint.

    Assuming id is your PK column and thus defined NOT NULL, count(*) does the same as count(id), a bit shorter and cheaper. I cast to integer, because count() will return a bigint.

    但是，在这里，我怀疑您不正确命名的列createdate实际上不是date而是timestamp(问题中缺少必需的表定义) .在这种情况下，用不同的词组查询会更有效:

    However, going out on a limb here, I suspect your inaccurately named column createdate is not actually a date but a timestamp (essential table definition is missing in the question). In that case it's much more efficient to phrase the query differently:

    CREATE OR REPLACE FUNCTION test(_source int, _days int)
      RETURNS integer AS
    $func$
    SELECT count(*)::int
    FROM   ad a
    WHERE  a.source = $1
    AND    a.createdate >= now() - interval '1 day' * $2
    AND    a.createdate <  now() - interval '1 day' * ($2 - 1)
    $func$ LANGUAGE sql STABLE;
    

    • 此表达式为 可精 ，因此高效的.它还可以在(createdate)上使用简单的索引，或者在(source, createdate)上使用更好的索引-对大表很重要.

      • This expression is sargable and thus more efficient. It can also use a plain index on (createdate), or better on (source, createdate) - important for big tables.

        还展示了另一种减去天数的方法.您可以乘以interval '1 day'.相关:

        Also demonstrating an alternative way to subtract days. You can multiply the interval '1 day'. Related:

        • 使用两个列值创建范围的查询

        这篇关于如何将参数传递给日期函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！