CODEWITHSUNDEEP
sqlpostgresql
Kupokev
Kupokev

Reputation: 189

Create function or stored proc with interval variable

I am new to PostgreSQL but have been working with MSSQL for years. I am currently working with PostgreSQL 13.

In MSSQL I have a script that creates an offset for dates so that I can create a fiscal calendar in the same date table as my regular calendar. I just add the offset to the date in the row and it adjusts my fiscal year as needed.

I am trying to preform something like this in PostgreSQL. I have some code working to do what I want. However, now I am trying to write a function or stored procedure to automate this code. I am having issues converting the code to work in a function or stored procedure.

My code I have working looks like this. This is a very trimmed down version of the code for illustration purposes.

DO $$
    DECLARE daydiff INTERVAL;
    DECLARE startdate DATE;
    DECLARE enddate DATE;
    DECLARE fiscalstartdate DATE;
BEGIN
    -- Populate these variables
    startdate:=(date '2017-01-01');
    enddate:=(date '2017-12-31');
    fiscalstartdate:=(date '2016-09-01');

    -- Calculate date diff
    daydiff:=make_interval(days => startdate - fiscalstartdate);
    
    -- Insert records into table
    INSERT INTO master.Calendar (
        full_date
        , year
        , fiscal_year
    )
    SELECT
        CAST(generate_series AS date) AS full_date
        , EXTRACT(YEAR FROM generate_series)::INTEGER AS year
        , EXTRACT(YEAR FROM generate_series + daydiff)::INTEGER AS fiscal_year
    FROM (
        SELECT generate_series(
            startdate::timestamp,
            enddate::timestamp,
            interval '1 days'
        )
    ) a;

END $$;

It does exactly what I want to do. However, I want to be able to call that in a function or stored procedure to automate rebuilding of tables and data. I then tried a few different things, but where I am at right now is this. Again, this is the trimmed down version, but still the issue is present.

CREATE OR REPLACE PROCEDURE create_calendar_table(startdate DATE, enddate DATE, fiscalstartdate DATE) AS $$ 
DECLARE 
    daydiff INTERVAL;
BEGIN
    -- Calculate date diff
    daydiff:=make_interval(days => startdate - fiscalstartdate);
    
    -- Insert records into table
    INSERT INTO master.Calendar (
        full_date
        , year
        , fiscal_year
    )
    SELECT
        CAST(generate_series AS date) AS full_date
        , EXTRACT(YEAR FROM generate_series)::INTEGER AS year
        , EXTRACT(YEAR FROM generate_series + daydiff)::INTEGER AS fiscal_year
    FROM (
        SELECT generate_series(
            startdate::timestamp,
            enddate::timestamp,
            interval '1 days'
        )
    ) a;

END $$ LANGUAGE SQL;

I receive an error of 'syntax error at or near "INTERVAL"'. I have tried a few things like trying to add a DO $$ in there and that did not seem to work well at all. Very well could be my ignorance on this version of SQL. The best I can tell is that I can't declare an INTERVAL type when creating a procedure or function like I can in the DO $$.

How can I get around this or what is the proper way of doing this in PostgreSQL? Any help is greatly appreciated.

Upvotes: 0

Views: 446

Answers (1)

user330315
user330315

Reputation:

You need language plpgsql - you can't use variables inside a SQL function.

Upvotes: 1

Related Questions