Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities - Mailing list pgsql-general
| From | cathy.hemsley@powerconv.alstom.com |
|---|---|
| Subject | Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities |
| Date | |
| Msg-id | OF8E5ED830.7A69B39B-ON80256F64.003A8342-80256F64.003D0F77@transport.alstom.com Whole thread Raw |
| List | pgsql-general |
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.
pgsql-general by date: