CODEWITHSUNDEEP
sqlpostgresqlplpgsql
piscu
piscu

Reputation: 94

how to use temporary table in procedure, Postgresql

i have a temporary table

   CREATE TEMPORARY TABLE temp_location
   (
      city VARCHAR(30),
      street VARCHAR(30)
   ) 
   ON COMMIT DELETE ROWS;

and i want to use this table in my procedure

CREATE OR REPLACE FUNCTION fexample(
    pcity character varying)
  RETURNS void AS
$BODY$
DECLARE 
BEGIN

    select * from temp_location where city = pcity;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

this method working in oracle, but not in postgresql

Upvotes: 1

Views: 2385

Answers (2)

Hambone
Hambone

Reputation: 16397

Unless I missed the boat, I think you want something like this:

CREATE OR REPLACE FUNCTION fexample(pcity character varying)
  RETURNS void AS
$BODY$
DECLARE
  rw temp_location%rowtype;
BEGIN

    for rw in select * from temp_location where city = pcity
    loop
       -- do stuff with rw
    next rw;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

If you declare the rowtype as a variable, you can then consume it within the query.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You could use:

CREATE OR REPLACE FUNCTION fexample(pcity VARCHAR(30))
  RETURNS TABLE(c VARCHAR(30),a VARCHAR(30)) AS
$$
    select * from temp_location where city = pcity;
$$
  LANGUAGE sql;

DBFiddle Demo

Upvotes: 4

Related Questions