PostgreSQL function to insert to table from another table has different structure

functionspostgresql

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,

  1. the selected year
  2. quarter 1 (1 to select, 0 to ignore)
  3. quarter 2 (1 to select, 0 to ignore)
  4. quarter 3 (1 to select, 0 to ignore)
  5. quarter 4 (1 to select, 0 to ignore)

For example: 

select get_info(2015,1,0,0,1)

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);

Best Answer

You don't actually need a function for this. You can do the same thing you're doing with your sample data with CTAS. I wouldn't use a function anyway. But, if you are here is how you do with a SQL function.

CREATE FUNCTION get_info(year int, quarter int[])
RETURNS int AS $$

  CREATE TEMPORARY TABLE tbl_temp AS
  SELECT *
  FROM foo
  WHERE year = get_info.year
    AND quarter = ANY(get_info.quarter);

  SELECT 0;

$$ LANGUAGE SQL;

Then just use

SELECT get_info(2015, '{1}');
SELECT get_info(2015, '{1,3,4}');

Etc.

Now, if you insist on the syntax given you can do that too.. We'll modify it slightly. PostgreSQL has bools. You should never make use 1 as true, when you actually have true.

CREATE FUNCTION get_info(
  year int,
  q1 bool DEFAULT false,
  q2 bool DEFAULT false,
  q3 bool DEFAULT false,
  q4 bool DEFAULT false
)
RETURNS int AS $$

  CREATE TEMPORARY TABLE tbl_temp AS
  SELECT *
  FROM foo
  WHERE year = get_info.year
    AND (
      (q1 AND quarter = 1)
      OR (q2 AND quarter = 2)
      OR (q3 AND quarter = 3)
      OR (q4 AND quarter = 4)
    );
  SELECT 0;

$$ LANGUAGE SQL;

Using plpgsql for this is way over kill.

Related Question