Date part in WHERE clause of a function

Question

I want to select persons from a table where the date is within a given month.
This is what I have so far, but it's not working:

CREATE OR REPLACE FUNCTION u7()
RETURNS character varying AS
$BODY$
    DECLARE
        data varchar=`data`;
        mes varchar=`2016-11-21`;
        incidencia varchar=`expulsions`;
        valor varchar;
    BEGIN
        EXECUTE `SELECT `
        ||quote_ident(data)
        ||`FROM `
        ||quote_ident(incidencia)
        ||` WHERE data IN(select date_part(`month`, TIMESTAMP $1))`
        INTO valor USING mes;
        return valor;
    END;
$BODY$
LANGUAGE plpgsql;

select * FROM u7();

why not `select persons from expulsions where data = date_part'month','2016-11-21');` ?.. why function? why it has constants? why even columna name is between constants?.. what is your goal?.. — Vao Tsun, Apr 27 '17 at 11:04
Those dreaded backticks are invalid in Postgres. String constant have to be put in single quotes: `'data'` https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS — , Apr 27 '17 at 11:16
Also: you are referencing a parameter `$1` but your function isn't declared with a parameter. — , Apr 27 '17 at 11:17
Also recommend using `date_trunc()::date` (to get the first day of the month) unless you don't care about the year. Either that or something like `to_char(mydatecolumn,'YYYYmm')`. — cole, Apr 27 '17 at 11:18
@a_horse_with_no_name Should be fine on the parameter. The "USING mes" passes "mes" to "$1" — cole, Apr 27 '17 at 11:19
Are you getting some error while executing from function ? what is data type of data column. You have to provide more information for better result. — Fahad Anjum, Apr 27 '17 at 14:30
Please [edit] the question to clarify. Remove the nonsense backticks, provide the table definition (`CREATE TABLE` statement) and your version of Postgres. — Erwin Brandstetter, Apr 28 '17 at 13:45

score 0 · Answer 1 · edited May 23 '17 at 12:17

Clean syntax for what you are trying to do could look like this:

CREATE OR REPLACE FUNCTION u7()
  RETURNS TABLE (valor text) AS
$func$
DECLARE
   data       text := 'data';    -- the first 3 would typically be function parameters
   incidencia text := 'expulsions';
   mes        timestamp = '2016-11-21';

   mes0       timestamp := date_trunc('month', mes);
   mes1       timestamp := (mes0 + interval '1 month');
BEGIN
   RETURN QUERY EXECUTE format(
     'SELECT %I
      FROM   %I
      WHERE  datetime_column_name >= $1
      AND    datetime_column_name <  $2'
    , data, incidencia)
   USING mes0, mes1;
END
$func$  LANGUAGE plpgsql;

SELECT * FROM u7();

Obviously, data cannot be a text column and a timestamp or date column at the same time. I use datetime_column_name for the timestamp column - assuming it's data type timestamp.

Aside from various syntax errors, do not use the construct with date_part(). This way you would have to process every row of the table and could not use an index on datetime_column_name - which my proposed alternative can.

See related answers for explanation:

Date part in WHERE clause of a function

1 Answers1