Results 1 to 2 of 2

Thread: PL/PGSQL help

  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Unanswered: PL/PGSQL help

    Hello all,

    I'm struck with a problem in PL/PGSQL. I have to analyse a database and remove inconsistencies in it. I want to write a function which will do this job. The skeleton of the function will be like this

    BEGIN

    FOR <<records>> IN (Select statement to find the inconsistencies) LOOP

    delete the inconsistences in the table and all its dependent tables

    END LOOP;

    END

    The problem I'm facing is that the select statement is a complex one in my case. So, I'm having trouble getting the postgres to accept this select statement. My whole function is this ,

    CREATE OR REPLACE FUNCTION "deleteTopic"()
    RETURNS void AS
    $BODY$DECLARE

    rec test%ROWTYPE;


    BEGIN

    FOR rec IN (select c.class_dat_id from mdc_data_item_class c where
    not exists (
    select null from mdc_topic t where t.topic_id = c.class_id
    )) LOOP

    DELETE FROM topic where id = rec.c.class_dat_id

    END LOOP;

    END$BODY$
    LANGUAGE 'plpgsql' VOLATILE

    While running this function, I get an error, saying that the schema rec doesn't exist. This is because of the joins. When I use a simple select statement, I can get to work and delete the data.

    So, can anybody tell me how to proceed with a complex select statement?

    Thanks a ton in advance

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You are not going to have the table alias "c" in your record.

    DELETE FROM topic where id = rec.class_dat_id

Posting Permissions

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