Return select * result in postgres function

Question

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?

user330315 · Accepted Answer · 2015-12-01 19:17:16Z

8

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

answered Dec 1, 2015 at 19:17

user330315

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

5 Comments

LuGaNO Over a year ago

that one did the job! i found a similar solution but yours has smaller syntax. Thanks!

LuGaNO Over a year ago

is there any way to get the result as table instead of : "(6,1,1,1,1,,,5,1,,,"2015-09-09 00:00:00",,,f,f,)" ??? I want the result as table to be parsed to java object. Thanks in advanced!

LuGaNO Over a year ago

I got it! it was stable.

user330315 Over a year ago

@LuGaNO: you need to use select * from get_data(1)

LuGaNO Over a year ago

thanks for your answear, but it wasn't that. It was putting the stable as i said before

LuGaNO · Accepted Answer · 2015-12-01 19:39:51Z

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;

Collectives™ on Stack Overflow

Return select * result in postgres function

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related