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();
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: