I have the following table with five columns representing locations and their status each quarter in a year.
-- id, location_id, status, year, quarter CREATE TABLE foo AS SELECT * FROM ( VALUES (1, 12, 1, 2014, 3), (2, 12, 1, 2014, 4), (3, 13, 0, 2015, 1), (4, 13, 0, 2015, 2), (5, 13, 1, 2015, 3), (6, 13, 0, 2015, 4), (7, 12, 1, 2015, 1), (8, 12, 1, 2015, 2) ) AS t(id,location_id,status,year,quarter);
I want to create a function that will extract data from the table based on passed parameters and store them in a new table that has different structure.
I created this function that will receive 5 parameters,
- the selected year
- quarter 1 (1 to select, 0 to ignore)
- quarter 2 (1 to select, 0 to ignore)
- quarter 3 (1 to select, 0 to ignore)
- quarter 4 (1 to select, 0 to ignore)
This option will select id_ location and status where quarter = 1 and 4 only. This is the function
CREATE or REPLACE FUNCTION get_info( get_year int, q1 int, q2 int, q3 int, q4 int) RETURNS VOID AS $$ DECLARE BEGIN CREATE TABLE IF NOT EXISTS tbl_temp ( parcelnum text, quart1 int, quart2 int, quart3 int, quart4 int); [ I have prolem with this part: INSERT INTO tbl_calc_temp( parcelnum text,quart1 int, quart2 int,quart3 int, quart4 int ) Valuse (select some_thing from foo where quarter = 1 or 4) ] END; $$ LANGUAGE PLPGSQL;
This function will create a table tbl_temp, then it should select rows from table foo based on based parameter then insert them to the table tbl_temp. For the quarter was not select, it values will be null .
Please help me writing the part I have problem with.
Note: The new table tbl_temp should have the following structure:
CREATE TABLE tbl_temp ( parcelnum text, year int, quart1 int, quart2 int, quart3 int, quart4 int);
I want to use:
INSERT INTO table(parcelnum,year, quart1 ,quart2, quart3,quart4) VALUES (values should be selected from foo and in case a quarter was not select or there is no record for a location in any quarter the value will be null);