How do I use a date as a variable in a postgreSQL function?

Question

I'm trying to write a function in postgreSQL 9.5 that takes a date as a parameter. My table has a column called inception_date, and I want the function to return all rows from the table where inception_date is greater than the date provided as the variable. Below is my function:

    CREATE OR REPLACE FUNCTION somefunc(date) RETURNS setof table AS 
    $BODY$
    DECLARE variable ALIAS FOR $1;
    BEGIN
    RETURN QUERY SELECT * FROM table WHERE inception_date > variable;
    END;
    $BODY$
    LANGUAGE 'plpgsql'; 

    SELECT somefunc('2014-07-02');

I haven't been able to find any info saying dates are handled differently in posgreSQL functions than other datatypes, but this function doesn't display any output, while the query

   SELECT * FROM table WHERE inception_date > '2014-07-02';

returns 15 rows. Does anyone know what might be going wrong here?

Actually SELECT somefunc('2014-07-02'); and SELECT * FROM table WHERE inception_date > '2014-07-02'; is not identical because the function returns the single column of the record type when query returns individual columns. How does you checking the result? — Abelisto
– Abelisto, Commented May 23, 2016 at 17:35
Well, it's a little old-fashioned to be using "ALIAS FOR" rather than just putting your parameter name in the function definition, and you don't want to quote the "plpgsql" language-spec, but this looks like it should work. Something is not as it looks. Start by calling it properly with "SELECT * FROM somefunc...". Then, simplify by (1) just returning the whole table then (2) explicitly checking against a constant date. Tell us what that does. — Richard Huxton
– Richard Huxton, Commented May 23, 2016 at 21:55

T.Z. · Accepted Answer · 2016-09-16 09:32:14Z

What is going here, You interpret Your code differently from PostgreSQL parser. For You '2014-07-02' is a date. For parser this is a srting. But he tries to be nice and when it see You use it for filtering a date column, it tries to interpret it as a date.

But given '2014-07-02', did You mean seventh day of february, or second day of july? He does not know it (and I do not know either). So, basically this is error on Your side and You should not do it this way.

What I would propose, is to create function with named parameter of date type, and use it as date. Then, when invoking this function, and having string as parameter, I would turn this string to date, telling PostgreSQL what I mean.

Examples and references to manual below.

    CREATE OR REPLACE FUNCTION somefunc(p_date date)
    RETURNS setof table 
    AS 
    $BODY$
        BEGIN
            RETURN QUERY SELECT * FROM table WHERE inception_date > p_date;
        END;
    $BODY$
    LANGUAGE 'plpgsql'; 

    SELECT somefunc(to_date('2014-07-02','YYYY-MM-DD'));

Here You can find more information on date formatting and creating of functions.

Kristoffer Legind · Accepted Answer · 2020-06-26 11:23:27Z

0

I have this 'pattern' for using pseudo variables containing dates. It does NOT rely on a function. I like using the WITH() CTE for this purpose:

WITH myvar (enddate) AS (VALUES(to_date('2020-06-26', 'YYYY-MM-DD')))
SELECT 'Total downloaded records', sum(d.total_records) FROM download_table d 
JOIN myvar m ON date(d.created) BETWEEN '2020-01-01' AND m.enddate

Please notice that the WITH Common Table Expression contains the date cast to a date format. You need this because you need to compare between date types and not date <= text.

answered Jun 26, 2020 at 11:23

Kristoffer Legind

12 bronze badges

Collectives™ on Stack Overflow

How do I use a date as a variable in a postgreSQL function?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related