Results 1 to 6 of 6

Thread: PL/SQL error

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Unanswered: PL/SQL error

    Hi guys,

    I have written a trigger - I was testing it and it failed. I fixed the typo - and it comes up with the exact same error.

    Now, earilier today I was reading the postgres docs about the fact postgres caches the function or something - and that you can clear this. I just can't find that page again!!!

    Anyway; I have tried a restart, and dropping it, create or replacing it etc...but doesn't work....any ideas?

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    post the syntax you have come up with.

    I hate those guessing games

    -Ed

  3. #3
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192
    Where the is the code for that trigger ? what version of Pgsql do u use , and what does it do ? please give us a well formed question to get the best solution
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  4. #4
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Should really know better shouldn't I...

    Code:
    -- Function: public.default_nodes_add()
    
    -- DROP FUNCTION public.default_nodes_add();
    
    CREATE OR REPLACE FUNCTION public.default_nodes_add()
      RETURNS trigger AS
    '
    DECLARE
        x RECORD;
    BEGIN
        
    
        --loop through the nodes
        FOR x IN SELECT * FROM user_type LOOP
    
    	EXECUTE \'INSERT INTO nodes_type (id,owner_user_id,node_id,user_type_id) VALUES(NEXTVAL(\'\'nodes_type_id\'\'),\' || NEW.owner_user_id || \',CURRVAL(\'\'nodes_id_seq\'\'),\' || x.id || \')\';
    
        END LOOP;
    
        RETURN NEW;
    END;
    '
      LANGUAGE 'plpgsql' VOLATILE;
    Anyway - I sorted it...it was a typo in the end (the above is the working version).

    Have another quick question: you may notice that I am inserting the nextval of a sequence in to an ID field - the ID field is a primary key, but it doesn't do this automagically (like it does on the other tables) which is weird.

    Here is the code for the table;

    Code:
    -- Table: public.nodes_type
    
    -- DROP TABLE public.nodes_type;
    
    CREATE TABLE public.nodes_type
    (
      id int4 NOT NULL,
      owner_user_id int4 NOT NULL,
      node_id int4 NOT NULL,
      user_type_id int4 NOT NULL,
      "addQuestionnaire" bool NOT NULL DEFAULT false,
      "editQuestionnaire" bool NOT NULL DEFAULT false,
      "enterQuestionnaire" bool NOT NULL DEFAULT true,
      "addResources" bool NOT NULL DEFAULT true,
      "editResources" bool NOT NULL DEFAULT true,
      "createFolder" bool NOT NULL DEFAULT true,
      "addUsers" bool NOT NULL DEFAULT true,
      CONSTRAINT nodes_type_pk PRIMARY KEY (id),
      CONSTRAINT nodes_type_node_id FOREIGN KEY (node_id) REFERENCES public.nodes (id) ON UPDATE RESTRICT ON DELETE CASCADE,
      CONSTRAINT nodes_type_owner_user_id FOREIGN KEY (owner_user_id) REFERENCES public.users (id) ON UPDATE RESTRICT ON DELETE CASCADE,
      CONSTRAINT nodes_type_user_type_id FOREIGN KEY (user_type_id) REFERENCES public.user_type (id) ON UPDATE RESTRICT ON DELETE CASCADE
    ) WITH OIDS;

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    Just noticed why....going to check (think its because the ID field is int4 not serial).

  6. #6
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    That was the problem ---> weird I only noticed it when I posted it on here!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •