CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-10
slevin
slevin

Reputation: 3888

Return TEMPORARY TABLE as table in pl/pgsql

The scenario is simple. Define a set of results as TEMPORARY TABLE, use this in another query as its IN part and then, after all the queries are finished, return that TEMPORARY TABLE.

In my case, define the delete_group, use it in DELETE and then return the delete_group.

I came up with this

CREATE  FUNCTION update_delete_honda
(modelId bigint, hondaId bigint)
RETURNS TABLE
(id bigint,name text) 
AS $$
DECLARE
 var_r record;
BEGIN    

CREATE TEMPORARY TABLE delete_group (honda_id bigint,name text)
ON COMMIT DROP;
INSERT INTO delete_group (honda_id,name)
SELECT honda_id,name FROM car WHERE service_id is null and drive_id is null;


 EXECUTE format(
   'UPDATE car SET model_Id = null WHERE honda_Id=$1'
) USING modelId , hondaId;

DELETE FROM suv WHERE id  IN (select honda_id from delete_group);

 FOR var_r IN(delete_group)  
 LOOP
   id := honda_id ; 
   name := name ;
   RETURN NEXT;
 END LOOP;

END;
$$
LANGUAGE plpgsql;

This does not work. I get ERROR: syntax error at or near "delete_group" about the FOR var_r IN(delete_group) line.

How do I fix this? I use PostgreSQL 10

Thanks

Upvotes: 0

Views: 62

Answers (1)

Heiko Jakubzik
Heiko Jakubzik

Reputation: 419

Change your loop to

FOR var_r IN SELECT * FROM delete_group ,

and use suv.id (instead of just id) in

DELETE FROM suv WHERE suv.id IN (select honda_id from delete_group);

You provided no definition of the tables car and suv, so I can't be sure if the function really does what you want it to ... but the above should be correct syntax.

Upvotes: 1

Related Questions