1

I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator.

I have tested that the actual query works inside the funcition if I use literal values, e.g. 2020 and 3 (March month number) in place of the variables.

Here is the function query I'm trying to use:

CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
) 
AS (
week int, 
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

When I try to call the function in a query, for example

SELECT * FROM get_month_events(2019, 8);

I'm getting this error:

ERROR:  column "mth" does not exist
LINE 7: WHERE extract(month from starts) = mth
                                           ^
QUERY:
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = mth
AND extract(year from starts) = yr
GROUP BY week, dow
ORDER BY week, dow
CONTEXT:  PL/pgSQL function get_month_events(integer,integer) line 3 at RETURN QUERY

Postgres does not recognize the parameter name inside the function query. How can I get it to reach the variable value?

It seems like it is just a stupid mistake I haven't spotted but so far I haven't been able to figure out why it does not let me access the variable.

1
  • Welcome to DBA.SE. Could you please edit your question and supply the DDL of the table events and maybe two or three records? Thanks. – John K. N. Sep 7 '20 at 10:59
0

Well in your function/procedure you are passing a string to the crosstab table function.

In the context of the string the value for mth can't be passed on as a variable in the function. You might have to concatenate the string like this:

CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
) 
AS (
week int, 
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

The relevant parts being:

...
WHERE extract(month from starts) = ' || mth || ' -- <<< HERE
AND extract(year from starts) = ' || yr || '     -- <<< AND HERE
GROUP BY week, dow
...

This way the value can be concatenated together with the string and executed in the context of the crosstab table function.

Working Solution

A working example can be found at this db<>fiddle

Create Table

create table events(
starts date,
eventtext varchar(20)
);

Insert Sample Data

insert into events(starts, eventtext) 
values
('2020-03-01', 'test1'),
('2020-03-01', 'test2')

Create Function/Procedure

CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
) 
AS (
week int, 
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

Select Function/Procedure

select get_month_events(2020,03)

Output

get_month_events
----------------
(9,2,,,,,,)
2
  • Escaping the variabes outside of the string seems to work. I was wondering if the query could be done without using the strings so that escaping the variables would not be necessary? – Sinipelto Sep 7 '20 at 12:13
  • At some point the variable is going to be have to "added" (concatenated) to the string value that you are passing to the crosstab table function. Your options would be: 1.) Add the whole select string as the parameter to the function/procedure. 2.) concatenate the string into a variable inside the function/procedure and assign that variable in the crosstab( here, ....) table function. – John K. N. Sep 7 '20 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.