Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities
| От | cathy.hemsley@powerconv.alstom.com |
|---|---|
| Тема | Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities |
| Дата | |
| Msg-id | OF8E5ED830.7A69B39B-ON80256F64.003A8342-80256F64.003D0F77@transport.alstom.com обсуждение исходный текст |
| Список | 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 по дате отправления: