PGSQL GENERAL 4 PROBLEM WITH INSERT RULES USING NEXTVAL GET SCHEMA NEW DOES NOT

PGSQL GENERAL 4 PROBLEM WITH INSERT RULES USING NEXTVAL GET SCHEMA NEW DOES NOT

From: cathy.hemsley@no-spam

Subject: Problem with Insert rules: using nextval: get schema *new* does not

Date: Wed, 08 Dec 2004 10:54:49 +0000

I am trying to see if it is possible to use rules to create complex updateable views covering two or more tables. Initially I created two test tables: one to hold CD names (cathyscds) and one to hold the track names/numbers/times for each cd (cathystracks) using the following SQL:

CREATE TABLE cathyscds ( cdname varchar,
cdartist varchar,
cdid int4) CREATE TABLE cathystracks ( cdid int4,
tracknumber int4,
trackname varchar,
tracktime float4)
I want views that would show the tracknames/times/numbers as arrays. I wrote a simple aggregate function makearray (basically copying the toarray example in the documentation), and created the view cdall: using SQL:

CREATE OR REPLACE VIEW cdall as SELECT cdname, cdid,
(SELECT makeArray (tracknumber) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)
AS numbers,
(SELECT makeArray (trackname) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)
AS names,
(SELECT makeArray (tracktime) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)
AS times FROM cathyscds;

This works fine. However, I hit lots of problems trying to create an Insert rule that would allow us to do something like:-
INSERT INTO cdall (cdname, names) VALUES ('cd name', '{track1 name, track2 name}');

The function must create the PK cdid and put one row into the cathyscds table, and zero,one or more rows into the cathystracks table.

I wrote a simple function (arrayToTable) to convert arrays to tables, to support this. This works fine stand-alone.
I created a sequence cd_seq to give the next cdid numbers.
I tried the rule:

CREATE OR REPLACE RULE insert_cdall AS ON INSERT TO cdall DO INSTEAD (
select nextval ('cd_seq') INTO new.cdid;
insert into cathyscds (cdid, cdname) VALUES (new.cdid, new.cdname);
insert into cathystracks (trackname, cdid)
select arrayToTable (new.names), currval ('cd_seq') as cdid;
);

Which I think should work. However, the 'select nextval...' statement appears in pgadmin under the Rules section as "select nextval ('cd_seq') as nextval;" which seems odd. It fails: gives error 'schema *new* does not exist. Why is this?

If I get round this problem by using nextval and currval then I get problems that I cannot get round. The above SQL gives error: function expression in FROM may not refer to other relations of same query level. I found a discussion thread 'nonexistent new relation in some places of rules' that hints that this is because the "select * from arrayToTable (new.names)" is being expanded into " select * from new, arrayToTable (new.names) ". Is this so, and if so, how do I get round it. Or should I give up and use functions instead?

We are using Postgres 7.4.1. on a Windows 2000 server. If you need the function code to help, please ask.

Thanks in advance Cathy Hemsley
:.________________
CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.

 I am trying to see if it is possible to use rules to create complex updateable views covering two or more tables. Initially I created two test tables: one to hold CD names (cathyscds) and one to hold the track names/numbers/times for each cd (cathystracks) using the following SQL:

 
 CREATE TABLE cathyscds
 ( cdname varchar,
 cdartist varchar,
 cdid int4) 
 CREATE TABLE cathystracks
 ( cdid int4,
 tracknumber int4,
 trackname varchar,
 tracktime float4) 
 
 I want views that would show the tracknames/times/numbers as arrays. I wrote a simple aggregate function makearray (basically copying the toarray example in the documentation), and created the view cdall: using SQL:

 
 CREATE OR REPLACE VIEW cdall as
 SELECT cdname, cdid,
 (SELECT makeArray (tracknumber) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)

 AS numbers,

 (SELECT makeArray (trackname) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)

 AS names,
 (SELECT makeArray (tracktime) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)

 AS times
 FROM cathyscds;
 
 This works fine. However, I hit lots of problems trying to create an Insert rule that would allow us to do something like:-

 INSERT INTO cdall (cdname, names) VALUES ('cd name', '{track1 name, track2 name}');

 
 The function must create the PK cdid and put one row into the cathyscds table, and zero,one or more rows into the cathystracks table.

 
 I wrote a simple function (arrayToTable) to convert arrays to tables, to support this. This works fine stand-alone.

 I created a sequence cd_seq to give the next cdid numbers.

 I tried the rule:
 
 CREATE OR REPLACE RULE insert_cdall AS ON INSERT TO cdall DO INSTEAD (

 select nextval ('cd_seq') INTO new.cdid;

 insert into cathyscds (cdid, cdname) VALUES (new.cdid, new.cdname);

 insert into cathystracks (trackname, cdid)

 select arrayToTable (new.names), currval ('cd_seq') as cdid;

 );
 
 Which I think should work. However, the 'select nextval...' statement appears in pgadmin under the Rules section as "select nextval ('cd_seq') as nextval;" which seems odd. It fails: gives error 'schema *new* does not exist. Why is this?

 
 If I get round this problem by using nextval and currval then I get problems that I cannot get round. The above SQL gives error: function expression in FROM may not refer to other relations of same query level. I found a discussion thread 'nonexistent new relation in some places of rules' that hints that this is because the "select * from arrayToTable (new.names)" is being expanded into " select * from new, arrayToTable (new.names) ". Is this so, and if so, how do I get round it. Or should I give up and use functions instead?

 
 We are using Postgres 7.4.1. on a Windows 2000 server. If you need the function code to help, please ask.

 
 Thanks in advance
 Cathy Hemsley
 
 
 
 
 
:.________________ 
CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.