CODEWITHSUNDEEP
postgresql
venkat
venkat

Reputation: 1263

Mapping with two tables using Postgres

I have two tables,

Table_1 contains like,

Project_name    Date          value
P1              15/06/2016
P2              25/04/2017
P3              18/06/2017
P4              12/05/2017

Table_2 contains like,

Name      Occ_June_2016  Occ_April_2017 Occ_May_2017  Occ_June_2017  
P1          8.1               7.5           6.5          8.2
P2          8.3               7.4           6.0          8.5
P3          8.6               7.1           6.1          8.1
P4          8.8               7.9           6.8          8.9

I want to get the value, based on mapping the project_name and the date.

Here is what i have tried. Converting table_1 date column to a particular format,

 SELECT to_char(to_date(a.date, 'DD-MM-YYYY'), 'mon_YYYY') from table_1 a ;

Output I got as,

jun_2016
apr_2017
jun_2017
may_2017

By using output above i want to search the column in table_2 by matching the column and name,

Am trying to get the columns based on partial match condition,

select column_name from information_schema.COLUMNS as c where c.TABLE_NAME = 'table_2' and c.COLUMN_NAME like '%occ_%';

Here is the output for the above query,

Occ_June_2016  
Occ_April_2017 
Occ_May_2017  
Occ_June_2017

Now i need to take the output of one query to the input of one query Here is where am stucked. Mapping the date based on name.

My output should be like,

Project_name    Date            value
P1              15/06/2016      8.1
P2              25/04/2017      7.4
P3              18/06/2017      8.1 
P4              12/05/2017      6.8

kindly give me a solution. Thanks in advance

Upvotes: 1

Views: 2540

Answers (1)

Dimitar Spasovski
Dimitar Spasovski

Reputation: 2132

This can be solved with a database function. First, lets create the tables and fill them with example data.

Creating and filling table_1

CREATE TABLE table_1 (project_name TEXT, Date DATE, value TEXT);
INSERT INTO table_1(project_name, Date) VALUES ('P1','06/15/2016'), ('P2','04/25/2017'), ('P3','06/23/2017'), ('P4','05/12/2017') ;

Creating and filling table_2

CREATE TABLE table_2 (name TEXT, occ_june_2016 DECIMAL, occ_april_2017 DECIMAL, occ_may_2017 DECIMAL, occ_june_2017 DECIMAL);
INSERT INTO table_2(name, occ_june_2016, occ_april_2017, occ_may_2017, occ_june_2017) VALUES
  ('P1',          8.1,               7.5,           6.5,          8.2),
  ('P2',          8.3,               7.4,           6.0,          8.5),
  ('P3',          8.6,               7.1,           6.1,          8.1),
  ('P4',         8.8,               7.9,           6.8,          8.9);

Next, we create the function:

CREATE OR REPLACE FUNCTION getData(projectName TEXT, projectDate DATE)
  RETURNS DECIMAL
  AS
  $$
  DECLARE
    columnName TEXT :=  'Occ_' || trim(to_char(projectDate, 'Month')) || '_' || to_char(projectDate, 'yyyy');
    selectQuery TEXT := 'SELECT %s FROM table_2 where name = ''%s'' LIMIT 1';
    returnValue DECIMAL;
  BEGIN
  selectQuery = format(selectQuery, columnName, projectName);
  EXECUTE selectQuery INTO returnValue;
  RETURN returnValue;
  END;
  $$ LANGUAGE 'plpgsql';

The function takes 2 arguments. The first one (projectName) represents the name of the project (for example 'P1'). The second one (projectDate) is the date on which we want to retrieve the data (for example '15/06/2016').

We start by generating the name of the date column in table 2 from the the value of the projectDate variable. Once we have a name, we generate a dynamic sql query by replacing the %s placeholders with the values of the columnName and projectName variables respectively.

We execute the query and store the result in the returnValue variable which this function returns.

After we created the tables and the function, we can execute the following query:

 SELECT project_name, Date, getData(project_name, Date) FROM table_1;

This query returns the following result:

project_name      date       getdata
P1             2016-06-15      8.1
P2             2017-04-25      7.4
P3             2017-06-23      8.1
P4             2017-05-12      6.8

Upvotes: 1

Related Questions