使用每行返回两个值的函数进行插入 [英] Insert using a function that returns two values per row

问题描述

此功能:

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;

此表:

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

这样的INSERT可以正常工作(插入5行):

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;

和这张桌子:

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

现在，我不能这样做:

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

我已经使函数返回setof mytype(创建带有两个日期的类型)，但它似乎做同样的事情. 我应该如何修改INSERT查询或函数才能使其正常工作?

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?

推荐答案

尝试使用:

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

因为已将a和b声明为要返回的表的列.

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

这篇关于使用每行返回两个值的函数进行插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！