CODEWITHSUNDEEP
sqlpostgresql
T D
T D

Reputation: 1150

PGSQL - How to use output of a select statement in another query?

I realized I didn;t do a good job in asking my question. So giving it a try again.

I am trying to create a pgsql function with below requirement.

I need to store the output of a sql statement in a row/record and use in another statement which selects from third table does some mathematical calculation and insert it second table.

exp:

    row = select * from table1;

    insert into table2(col21,col22,col23,col24,col25)
    values(
    select 
    (col31 - row.col11)/row.col15,
    (col32 - row.col12)/row.col14,
    (col33 - row.col13)/row.col13,
    (col34 - row.col14)/row.col12,
    (col35 - row.col15)/row.col11
    from table3
);

I would like to know how can I achieve it with pgsql.

Thanks in advance.

Upvotes: 1

Views: 846

Answers (4)

Steve Kline
Steve Kline

Reputation: 805

Should be fairly standard sql.

INSERT INTO TABLE2 (col1, col2, col3, col4, col5) 
VALUES (
         SELECT *       -- Assuming there's only 5 Columns from Table1
         FROM TABLE1
       )

If you're looking for some sort of temporary table to alter data later and be able to revert back;

 CREATE TEMP TABLE temp_table1 AS
 SELECT * from table1;

Further note: If your first column is an automatically incremented pkey and you have data in your 2nd table already - you should just insert values for non-increment columns or you'll have other errors.

INSERT INTO TABLE2 (col2, col3, col4, col5) 
VALUES (
         SELECT col2, col3, col4, col5       -- Assuming there's only 5 Columns from Table1 and Col1 is a pkey.
         FROM TABLE1
       )

Upvotes: 1

T D
T D

Reputation: 1150

Thank you everyone for your answer. Below is what I came up with. Ref : https://www.postgresql.org/docs/8.2/static/plpgsql-declarations.html

DECLARE
row table1%rowtype;

BEGIN
select * into strict row from table1;

insert into table2(col21,col22,col23,col24,col25)
    values(
    select 
    (col31 - row.col11)/row.col15,
    (col32 - row.col12)/row.col14,
    (col33 - row.col13)/row.col13,
    (col34 - row.col14)/row.col12,
    (col35 - row.col15)/row.col11
    from table3
);

END;

Upvotes: 0

Bohemian
Bohemian

Reputation: 425318

No need for anything fancy; normal SQL has you covered:

insert into table2(col1,col2,col3,col4,col5)
select
    (b.col31 - a.col11)/a.col15,
    (b.col32 - a.col12)/a.col14,
    (b.col33 - a.col13)/a.col13,
    (b.col34 - a.col14)/a.col12,
    (b.col35 - a.col15)/a.col11
from table1 a
cross join table3 b

Upvotes: 2

Paresh
Paresh

Reputation: 654

Basic SQL:

INSERT INTO table2 (column1, column2, column3, ...)  
SELECT column1, column2, column3, ...  
FROM table1  
WHERE condition;

Source: https://www.w3schools.com/sql/sql_insert_into_select.asp

Upvotes: 0

Related Questions