0

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);
  • Is your goal to store this in a temporary table, or to return the result? And are you fixated on that syntax or is any syntax that allows an end user to accept multiple quarters acceptable? – Evan Carroll Dec 22 '16 at 0:38
  • Thank your answer I will use the temp table for more operations in the same function and this temp table might be very large so to avoid memory overflow I created it then I will drop it at the end – Eyla Dec 22 '16 at 1:38
1

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.

  • There is a requirement from developer team to use 1 or 0 I don't why – Eyla Dec 22 '16 at 1:47
  • Then just switch it from bool to int, and then (q1 AND quarter = 1) to (q1 = 1 AND quarter = 1). But, ask your developers. This is a bad idea. – Evan Carroll Dec 22 '16 at 1:50
  • you are using create as to create the temp tbale and I want to have new structure for the it as mentioned in the question. I know how to use create as but I need it different way – Eyla Dec 22 '16 at 14:57

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.