Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities

Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities

From
cathy.hemsley@powerconv.alstom.com
Date:

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.