Insert using a function that returns two values per row

Question

This function:

CREATE OR REPLACE FUNCTION fn_test1()
  RETURNS SETOF date AS
$BODY$
declare
i int;
begin

i:=0;
while i<5 loop
   return next '2001-01-02'::date;
   i:=i+1;
end loop;


end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

This table:

CREATE TABLE teste1
(
  teste1_id serial NOT NULL,
  num integer,
  fn_date date)

An INSERT like this works just fine (inserting 5 rows):

Insert into teste1(num,fn_date)
select 1, fn_test1();

But if I want to have a function that returns two dates in a row, and a table that has 2 columns of dates how should I do that? I've made this so far:

CREATE OR REPLACE FUNCTION fn_test2()
  RETURNS TABLE(a date, b date) AS
$BODY$
declare
_start_date date;
_end_date date;
begin

_start_date:='2001-01-01'::date;
_end_date:='2002-01-01'::date;

i:=0;
while i < 5 loop
    return query(select _start_date,_end_date);
    i:=i+1;
end loop;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

And this table:

CREATE TABLE teste2
(
  teste2_id serial NOT NULL,
  num integer,
  start_date date,
  end_date date)

Now, I can't do this:

INSERT INTO teste2(num,start_date,end_date)
SELECT 1, fn_test2();

I've made the function return setof mytype (creating a type with two dates) but it seems to do the same thing. How should I modify the INSERT query or the function to make this work?

Bahrain

Answer 1

Try using:

INSERT INTO teste2(num,start_date,end_date)
SELECT 1, f.a, f.b FROM fn_test2() AS f;

since you've declared a and b as columns of the table being returned.

Tonneau

Answer 2

To access fields of a (well known) composite type, you need to wrap the identifier in parentheses. Without parenthesis the identifier before the dot would be taken to be a table name per SQL syntax rules. This would work:

SELECT 1, (fn_test2()).*

BTW, your dummy function could be simpler:

CREATE OR REPLACE FUNCTION fn_test2()
  RETURNS TABLE(a date, b date) AS
$func$
BEGIN

a := '2001-01-01'::date;
b := '2002-01-01'::date;

FOR i in 0 .. 4 LOOP
    RETURN NEXT;
END LOOP;

END
$func$ LANGUAGE plpgsql;

Or use this simple SQL statement with generate_series() to the same effect:

SELECT 1, '2001-01-01'::date AS a, '2002-01-01'::date AS b
FROM   generate_series(0,4);

Wiseman

I've made a RAISE NOTICE in the function and it seems that the function has been called two times the way you've answered. The query @sandesh247 wrote doesn't seem to have this problem. – Bahrain

@NewK: That's a weakness of the current Postgres version. * is resolved to individual columns and each one is called separately. A subquery like @sandesh247 provided avoids multiple calls. – Wiseman