CODEWITHSUNDEEP
sqlwindowspostgresql
Svetlana
Svetlana

Reputation: 51

Call a function or view with a parameter

I want to create function or view which will show some information particular year monthly.

CREATE OR REPLACE VIEW name_view AS 
    SELECT d_name,
    SUM("Jan") "Jan",
    SUM("Feb") "Feb"
    ...
    FROM (
    select distinct d_name,
      COUNT(CASE WHEN EXTRACT (MONTH FROM h_date) = '01' THEN query_string ELSE NULL END) "Jan",
      COUNT(CASE WHEN EXTRACT (MONTH FROM h_date) = '02' THEN query_string ELSE NULL END) "Feb",
    ..
       from h
           inner join d on h_d = d_id
                WHERE EXTRACT (YEAR FROM h_date) = '2015' 
            GROUP BY  d_name) sub query
    GROUP BY d_name

How can I call view like this without

WHERE EXTRACT (YEAR FROM h_date) = '2015'

but with the year of the call? For example

SELECT * FROM name_view ('2015')

Upvotes: 0

Views: 333

Answers (2)

Madhusudan
Madhusudan

Reputation: 4815

You can try this function passing date as a parameter:

CREATE OR REPLACE FUNCTION "show_info"("DATE" date)
  RETURNS SETOF show_info AS
$BODY$SELECT d_name,
    SUM("Jan") "Jan",
    SUM("Feb") "Feb"
    ...
    FROM (
    select distinct d_name,
      COUNT(CASE WHEN EXTRACT (MONTH FROM h_date) = '01' THEN query_string ELSE NULL END) "Jan",
      COUNT(CASE WHEN EXTRACT (MONTH FROM h_date) = '02' THEN query_string ELSE NULL END) "Feb",
    ..
       from h
           inner join d on h_d = d_id
                WHERE EXTRACT (YEAR FROM h_date) = $1 
            GROUP BY  d_name) subquery
    GROUP BY d_name
;$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

You can call it like 

select * from show_info(date)

here date will be whatever date you want to pass

Upvotes: 1

massie
massie

Reputation: 556

You should use YEAR in the SELECT and when you SELECT from the view you use WHERE year = 2015. 

CREATE OR REPLACE VIEW name_view AS 
SELECT d_name,
SUM("Jan") "Jan",
SUM("Feb") "Feb",
YEAR,
....

Use of view:

SELECT * 
FROM name_view 
WHERE year = '2015';

Upvotes: 1

Related Questions