Why does Partition Table wont accept generate_series() function ?

Hi All,

I am creating the partition table and I want to insert the multiple rows with generate_series function. In my function am inserting 10 rows, in which 5 should go in child1 table and 5 to another child2 table according to the rule set for Partition Table. Please find the example given below as generate series is not allowed as expected.

Creating Partition Tables

postgres=# create table parent(id int);
CREATE TABLE
postgres=# create table child1(check ( id < 6)) inherits (parent);
CREATE TABLE
postgres=# create table child2(check (id >= 6 and id <= 10)) inherits (parent);
CREATE TABLE

Creating Rule for Partition Table

postgres=# create or replace rule insert_child1 as on insert to parent where ( id < 6) do instead insert into child1 values(new.id);
CREATE RULE
postgres=# create or replace rule insert_child2 as on insert to parent where ( id >= 6 and id <= 10) do instead insert into child2 values(new.id);
CREATE RULE

If I insert individually, my partition works

postgres=# insert into parent values (1), (6);
INSERT 0 0
postgres=# \x
Expanded display is on.
postgres=# select * from child1, child2;
-[ RECORD 1 ]
id | 1
id | 6

Here am using generate_series for insertion, this fails

postgres=# insert into parent values(generate_series(1,10));
ERROR: set-valued function called in context that cannot accept a set

Is there any work-around for this…
Thanks in Advance

Regards
Raghavendra

Try:

insert into parent select * from generate_series(1,10);

--Scottie

Hi Scott,

Thank you..

Is there any particular reason why it is not accepted in VALUES clause..

Regards
Raghavendra

Raghavendra_R wrote:Hi Scott,

Thank you..

No problem

Is there any particular reason why it is not accepted in VALUES clause..

Yeah, 'generate_series()' is a special type of function called a Set-Returning function (SRF). These types of functions return a set of items, not just one item. In actuality, an SRF is basically the same thing as a table. The problem with SRF's is that they need to be referenced as if they are a table or there is an error.

--Scottie

Hi Scott,

Got cleared..!! You Rock....

Regards
Raghavendra