Link to home
Start Free TrialLog in
Avatar of pvsbandi
pvsbandiFlag for United States of America

asked on

Delete rows dynamically

Hi,

  We are on Postgres 9.6 Linux database.
Have a requirement to create a function and pass  cc_case_ID as the parameter.
requirement then, is to to delete all the records from all the tables in a schema, where the cc_case_ID is equal to the parameter value passed.

Can someone kindly help with the solution?
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

you want to create a gig for this???
Avatar of pvsbandi

ASKER

What's a gig?
have you read the plpgsql documentation?  what you are asking is not very difficult, but one would need to know how the foreign keys are set up.
I understand that there would be challenges with respect to the referential integrity, but would like to get the basic logic working first.
I have created a function, and it compiled fine.
But when i execute it , throws error: Help please?

" ERROR: missing FROM-clause entry for table "a"
  Where: PL/pgSQL function delete_rows_individual_id_lookup(integer) line 11 at EXECUTE
"
CREATE OR REPLACE FUNCTION public.delete_rows_individual_id_lookup(v_cs_id INTEGER)
  RETURNS VOID AS
$func$
DECLARE
   tb_name text;
BEGIN
   FOR tb_name IN
      SELECT table_name
        FROM information_schema.columns
       WHERE table_schema = 'public'
         AND column_name = 'cs_id'
   LOOP
      EXECUTE
       'DELETE FROM public.'||quote_ident(tb_name) ||
			' A WHERE exists (SELECT 1 FROM public.DEL_TEST_MSTR B
								where A.ind_id = B.ind_id '
							   || CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns B
													 WHERE A.table_name = B.table_name
													   AND A.table_name = quote_ident(tb_name)
													   AND B.column_name = 'cs_id'
												   )
								   THEN chr(10)||' and A.cs_id = B.cs_id'
								   ELSE ''
								   END
								  || chr(10)|| 'and B.cs_id = '||cast(v_cs_id as int)

                || ' )' 
		     ||' from information_schema.columns A
            where column_name = ''cs_id''';
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Open in new window

SOLUTION
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Could you please give me a simple example code that works?
I will then model my function based in that
If you set up the relationships correctly, you can delete the row from only the parent table and cascade delete will delete all related child rows.
Hi Pat,

     We don't want the cascade to be in for all the time.
This deletion effort is only to delete the test cases seeded in production, so will be a one-time effort each time, a release goes out.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial