CODEWITHSUNDEEP
sqlpostgresqlfunction
LuGaNO
LuGaNO

Reputation: 109

Return select * result in postgres function

i am trying to return a query result inside a postgres function. I tried this and worked perfectly: 

CREATE OR REPLACE FUNCTION getIncomingAreaMovements(v1 integer)
RETURNS integer AS $$
DECLARE
BEGIN
    return id_part_movement From part_movement where  id_area_final_destination = $1;
END;
$$ LANGUAGE plpgsql;

The things is that I need something to return the result of:

Select * From part_movement where  id_area_final_destination = $1;

what return do I need or what should I change to achieve this?

Upvotes: 5

Views: 20331

Answers (2)

LuGaNO
LuGaNO

Reputation: 109

The one that worked was: 

CREATE OR REPLACE FUNCTION getIncomingAreaMovements(integer)
RETURNS table (id_part_movement integer ,
  id_part integer,
  id_area_origin integer ,
  id_area_destination integer ,
  id_user_sender integer,
  id_user_receiver integer,
  id_user_cancel integer,
  id_part_order integer,
  id_area_final_destination integer,
  cause character varying(50),
  description character varying(255),
  start_date timestamp,
  end_date timestamp,
  cancelation_cause character varying(255),
  canceled boolean,
  rejected boolean,
  id_block_movement integer)

AS $$
DECLARE
BEGIN
    return query select pm.*  
    From part_movement pm where  pm.id_area_final_destination = $1;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

user330315
user330315

Reputation:

This can be done with a simple SQL function:

CREATE OR REPLACE FUNCTION get_data(v1 integer)
RETURNS setof part_movement 
AS 
$$
   Select * 
   From part_movement 
   where id_area_final_destination = v1;
$$ LANGUAGE sql;

More details and examples can b found in the manual:
http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET

Upvotes: 8

Related Questions