Calling Postgres Stored Procedure with arguments and insert values from a given select

Question

Say I have a select statement.

SELECT sensorname, starttime from sensors where id = ?;

It returns for instance:

Value1 | 123456789
Value1 | 987465465

(starttime is a timestamp)

Now I'd like to create a StoredProcedure within my Postgres.

someProcedure(argumentId){
   Result r = SELECT sensorname, starttime from sensors where id = ?;

}

Then it should loop over the resultSet, take the results and insert them into another table and finally remove the old ones from the origin table.

Is this possible with a storedProcedure?

Edit: I need a stored Procedure for this.

something like:

CREATE OR REPLACE FUNCTIONrollupMinutes(id bigint) RETURNS void AS $$

BEGIN
    var qry_rsult = SELECT sensorname, starttime from sensors where id = id;
    insert into rollup(qry_result)   
END;
$$ LANGUAGE plpgsql;

Vivek S. · Accepted Answer · 2016-02-12 11:59:23Z

1

You can do that using CTE like below(Only if you want to avoid a function)

WITH cte (id)
AS (
    INSERT INTO another_table (sensorname,starttime)
    SELECT sensorname
          ,starttime
    FROM sensors WHERE id = id 
    returning id;
    )
DELETE
FROM
sensors
WHERE id IN (SELECT *FROM cte);

OR

By creating a function it can be like

create or replace function fn(id int) returns void as
$$
insert into another_table(sensorname,starttime)  
SELECT sensorname, starttime from sensors where id =id;
delete from sensors where id =id;
$$
language sql

Usage:

select fn(12)

edited Feb 12, 2016 at 11:59

answered Feb 12, 2016 at 11:52

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christian Kellner Over a year ago

I don't get how this cte plays together with the function

Vivek S. Over a year ago

CTE is not working here with a function it's a different approach, I've added CTE method just for your information

Christian Kellner Over a year ago

ok. well thank you. What I actually wanted is selecting a subset from a table and inserting it into another table. then delete the original data. I can't see this within your answer.. Especially within the function (which is mostly intersting for me)

Vivek S. Over a year ago

@Christian insert into another_table(sensorname,starttime) SELECT sensorname, starttime from sensors where id =id; will select ` sensorname, starttime` from sensors table and insert them to another_table's sensorname,starttime(Or your desired column1,column2).After that delete from sensors where id =id; will delete values from table sensors

Collectives™ on Stack Overflow

Calling Postgres Stored Procedure with arguments and insert values from a given select

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related